I have the following data:
event_date | isin | event_month | hc |
11/09/1995 | FI0009000012 | -12 | 0 |
11/09/1995 | FI0009000012 | -11 | 0 |
11/09/1995 | FI0009000012 | -10 | 0 |
11/09/1995 | FI0009000012 | -9 | -0.034 |
11/09/1995 | FI0009000012 | -8 | -0.079 |
11/09/1995 | FI0009000012 | -7 | -0.046 |
11/09/1995 | FI0009000012 | -6 | -0.04 |
11/09/1995 | FI0009000012 | -5 | -0.047 |
11/09/1995 | FI0009000012 | -4 | 0.14 |
11/09/1995 | FI0009000012 | -3 | -0.176 |
11/09/1995 | FI0009000012 | -2 | 0.2 |
11/09/1995 | FI0009000012 | -1 | 0.11 |
11/09/1995 | FI0009000012 | 0 | 0.55 |
5/11/2009 | FI0009005078 | -9 | 0 |
5/11/2009 | FI0009005078 | -8 | 0.12 |
5/11/2009 | FI0009005078 | -7 | 0.28 |
5/11/2009 | FI0009005078 | -6 | 0.31 |
5/11/2009 | FI0009005078 | -5 | 0.44 |
5/11/2009 | FI0009005078 | -4 | 0.24 |
5/11/2009 | FI0009005318 | -12 | 0.262 |
5/11/2009 | FI0009005318 | -11 | 0.11 |
5/11/2009 | FI0009005318 | -10 | 0.32 |
5/11/2009 | FI0009005318 | -9 | -0.04 |
9/05/2011 | FI0009003305 | -5 | 0.032 |
9/05/2011 | FI0009003305 | -4 | 0.02 |
9/05/2011 | FI0009003305 | -3 | 0.44 |
9/05/2011 | FI0009003305 | -2 | 0.87 |
9/05/2011 | FI0009003305 | -1 | 0.92 |
and I like to create a table like this:
event_date | isin | 0 | -1 | -2 | -3 | -4 | -5 | -6 | -7 | -8 | -9 | -10 | -11 | -12 |
11/09/1995 | FI0009000012 | 0.55 | 0.11 | 0.2 | -0.176 | 0.14 | -0.047 | -0.04 | -0.046 | -0.079 | -0.034 | 0 | 0 | 0 |
5/11/2009 | FI0009005078 |
|
|
|
| 0.24 | 0.44 | 0.31 | 0.28 | 0.12 | 0 |
|
|
|
5/11/2009 | FI0009005318 |
|
|
|
|
|
|
|
|
| -0.04 | 0.32 | 0.11 | 0.262 |
9/05/2011 | FI0009003305 |
| 0.92 | 0.87 | 0.44 | 0.02 | 0.032 |
|
|
|
|
|
|
|
Can anyone please help? I have attached the test data in SAS format. Thanks in advance for your help.!
Do you want this output table for a report, or as a SAS data set?
This would give you the desired report:
proc report data=test_data;
columns event_date isin event_month,hc;
define event_date/group order=data;
define isin/group order=data;
define event_month/across;
define hc/sum;
run;
Do you want this output table for a report, or as a SAS data set?
This would give you the desired report:
proc report data=test_data;
columns event_date isin event_month,hc;
define event_date/group order=data;
define isin/group order=data;
define event_month/across;
define hc/sum;
run;
@bd_user_10 wrote:
This worked fine! However, I want this output table as a SAS data set.
Looks like someone else provided that code. However, I figured you want it as a table because you can't have a variable name as -1 in SAS, as your original table indicated.
/* I just added an out= option to the proc report statement in @PaigeMiller 's reply and then modified the new dataset by assigning variable names and labels to the vars created by proc report */
libname dat '~/dat/';
proc report data=dat.test_data out=transtest (drop=_BREAK_);
columns event_date isin event_month,hc;
define event_date/group order=data;
define isin/group order=data;
define event_month/across;
define hc/sum;
run;
proc contents data=transtest;
run;
%macro ren;
%do i=0 %to 12;
rename _C%eval(&i+3)_ = V_&i;
label V_&i="V_&i~(-&i)";
%end;
%mend;
proc datasets library=WORK nolist;
modify transtest;
%ren ;
quit;
proc print label split='~';
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: