BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sky_sas
Calcite | Level 5

 

data have;
input cust $ month apple $ orange $ melon $;
datalines;
1 1 Y . .
1 2 . Y Y
1 3 . . .
2 1 . . .
2 2 Y . .
2 3 . . Y
;
run;

I am having a dataset with purchasing record over 100 product in total of 3 months. I would like to consolidate them into one record for each customer. If the customer have purchase that product in any one of the three months, it would show 'Y' in the result dataset. The result should be like this:

 

 

Have:

cust month apple orange melon

1     1          Y
1     2                   Y          Y
1     3 
2     1 
2     2          Y 
2     3                               Y

 

Want:

cust apple orange melon

1       Y      Y          Y

2       Y                  Y

 

As there are over 100 dynamic columns in the real dataset. I was trying to put all the column into macro variable. However, I do not know how I could utilize these macro variable.

   proc sql noprint;
       select NAME into :_PRODUCT from dictionary.columns
       where memname="HAVE" 
       and libname = "WORK" and (name not in ("CUST","MONTH");
   quit;

I think it would be more effective if I could utilize macro variable as I do not need to manually input all the column name when I am consolidating the records

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

Marvellous things can be done with the data step:

data work.master;
	set have;
	by cust;
	
	if first.cust;
run;

data work.want;
	update master have;
	by cust;
	
	drop month;
run;

The update statement expects only one observation per BY-variable, so we create a "master"-dataset first. Then we use the fantastic feature of update that takes care of missing values.

View solution in original post

1 REPLY 1
andreas_lds
Jade | Level 19

Marvellous things can be done with the data step:

data work.master;
	set have;
	by cust;
	
	if first.cust;
run;

data work.want;
	update master have;
	by cust;
	
	drop month;
run;

The update statement expects only one observation per BY-variable, so we create a "master"-dataset first. Then we use the fantastic feature of update that takes care of missing values.

Ready to join fellow brilliant minds for the SAS Hackathon?

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

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 330 views
  • 3 likes
  • 2 in conversation