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
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;
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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.