I have two tables : T1 and T2 As below-- T1:
ID | Age
1 | 22
2 | 23
3 | 24
T2: 22, 23, 24 are column names.
ID | 22 | 23 |24
1 | 10 | 50 | 120
2 | 20 | 60 | 130
3 | 30 | 70 | 220
Now I need to create a third table as : Based on Age and ID of table 1, value in column is to be selected and column Dev is to be populated. Here column selection is done based of the row value of age in table 1.
Therefore answer would be:
ID | Age| Dev
1 | 22 | 10
2 | 23 | 60
3 | 24 | 220
For More Understanding:
1) Sas variable names like 22 23 .. are sas invalid names. You probably imported those tables from excel.
I believe that by importing the variables will be var1 var2 .. or alike. You may need to rename those variables.
In order to display my solution I shall use names A22 A23 .. which are sas valid names.
2) possible solution:
Step 1 transpose table from wide to long:
proc transpose data=T2 out=temp;
by ID AGE;
name = 'DEV';
var A22 - A24; /* first age up to last age */
run;
proc sort data=temp; by ID AGE; run;
thus will result into table like:
ID AGE DEV 1 22 10 1 22 20 1 22 30 2 23 50 2 23 60 2 23 70 ...
Next step will be to merge table T1 with the temp table by ID and AGE:
data want;
merge T1 temp;
by ID AGE;
run;
It is not tested. In case of issues post the log.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.