BookmarkSubscribeRSS Feed
nyc_user
Calcite | Level 5

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

3 REPLIES 3
Jagadishkatam
Amethyst | Level 16

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;
 

 

 

Thanks,
Jag
PaigeMiller
Diamond | Level 26

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;

 

--
Paige Miller
nyc_user
Calcite | Level 5
This is great.... thank you both for a quick response..

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1244 views
  • 2 likes
  • 3 in conversation