topic Need help with Macro in SAS Programming

Need help with Macro

nyc_user — Thu, 23 Aug 2018 17:52:44 GMT

Hello SAS group… I need help with sas macro to create a single line for a client record depending on one column variable… There is a different number of elements each day per each client that is changing from day to day based on client’s activity.

There could be a different number of client types and elements per client each day, and I need to summarize the data based on the client number per day per client type.

Example data:

Client Number	Client type	Element Name	Element Value	Element Date
0001	A	Months	12	20180801
0001	A	Count	500	20180801
0001	A	Records	10	20180802
0001	B	Months	6	20180801
0001	B	Count	600	20180801
0001	B	Records	1	20180801
0001	B	Cycle	2	20180801
0001	B	Code	N	20180801
0002	A	Months	2	20180801
0002	B	Code	E	20180802

Data I want to look like:

Client Number	Client type	Months	Count	Records	Cycle	Code	Element Date
0001	A	12	500				20180801
0001	A			10			20180802
0001	B	6	600	1	2	N	20180801
0002	A	2					20180801
0002	B					E	20180802

Thanks in advance

Re: Need help with Macro

Jagadishkatam — Thu, 23 Aug 2018 18:02:55 GMT

you could try proc transpose as below


proc sort data=have;
by Client_Number Client_type Element_Date;
run;

proc transpose data=have out=want;
by Client_Number Client_type Element_Date;
id Element_Name;
var Element_value;
run;

Re: Need help with Macro

PaigeMiller — Thu, 23 Aug 2018 18:07:13 GMT

It is a mistake to think you need to resort to macros for this. You do not need macros. Don't make your programming more complicated than it needs to be.

This is a PROC TRANSPOSE with a BY statement. This assumes your data is properly sorted.

/* UNTESTED CODE */

proc transpose data=have out=want;
     by clientnumber clienttype elementdate;
     var elementvalue;
     id elementname;
run;

Re: Need help with Macro

nyc_user — Thu, 23 Aug 2018 18:22:09 GMT

This is great.... thank you both for a quick response..