Join two data sets with SQL
This example creates two data sets MyTable1 and MyTable2 in ADaMSoft, having a common variable named user. Then the SQL step is called to create a data base in the ADaMSoft path MyDB (defined in "c:/temp"). The data base is named mydb and the content of MyTable1 and MyTable2 will be copied in the data base.
In a next SQL step the two tables will be joined, by considering the common variable user; the results will be also displayed in ADaMSoft Output area.
In order to run this example just paste the following statements in the Command area and press the button: EXECUTE.
This example creates two data sets MyTable1 and MyTable2 in ADaMSoft, having a common variable named user. Then the SQL step is called to create a data base in the ADaMSoft path MyDB (defined in "c:/temp"). The data base is named mydb and the content of MyTable1 and MyTable2 will be copied in the data base.
In a next SQL step the two tables will be joined, by considering the common variable user; the results will be also displayed in ADaMSoft Output area.
In order to run this example just paste the following statements in the Command area and press the button: EXECUTE.
|
path mydb=c:\temp; dataset out=mytable1; newvar age=num; newvar name=text; age=19; name="John"; output; age=48; name="Marco"; output; run; dataset out=mytable2; newvar weight=num; newvar name=text; weight=82; name="John"; output; weight=75; name="Marco"; output; run; sql dict=mytable1 dict=mytable2 outdb=mydb.db_test; run; sql outdb=mydb.db_test; CREATE CACHED TABLE mytable3 AS (SELECT mytable1.*, mytable2.weight FROM mytable1 inner join mytable2 on mytable1.name=mytable2.name) WITH DATA; select * from mytable3; run; |