topic Re: How to create a specific table? in SAS Programming

How to create a specific table?

bd_user_10 — Tue, 24 Dec 2019 16:23:41 GMT

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.!

Re: How to create a specific table?

PaigeMiller — Tue, 24 Dec 2019 16:42:00 GMT

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;

Re: How to create a specific table?

bd_user_10 — Wed, 25 Dec 2019 13:36:18 GMT

This worked fine! However, I want this output table as a SAS data set.

Re: How to create a specific table?

ghosh — Wed, 25 Dec 2019 20:56:45 GMT

/* 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;

Re: How to create a specific table?

PaigeMiller — Wed, 25 Dec 2019 20:12:28 GMT

@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.

Re: How to create a specific table?

bd_user_10 — Thu, 26 Dec 2019 03:45:57 GMT

Thanks for showing me how to create the dataset from the report. I appreciate that!