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.
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.
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.