Hello, I have a data as followed.
ID|111|112|113|211|222 001|0.03|0|0|3.2|2.1 002|0|0|4.21|35|0 003|24.1|0|0|0|11.7
ID is a customer ID and other column names are product ID numbers. I want to produce data which would be as followed.
ID|Product ID|value 001|111|0.03 001|211|3.2 001|222|2.1 002|113|4.21 002|211|35 003|111|24.1 003|222|11.7
My problem is that the code I am using is very long due to having so many product ID numbers in the real data. At the moment the code is as followed.
proc sql;
create table products as
select
ID, '111' as product ID, '111'n as value
from _data
where '111'n > 0 and ID > 0
union all
select
ID, '112' as product ID, '112'n as value
from _data
where '112'n > 0 and ID > 0;
union all
select
ID, '113' as product ID, '113'n as value
from _data
where '113'n > 0 and ID > 0
union all
select
ID, '211' as product ID, '211'n as value
from _data
where '211'n > 0 and ID > 0
union all
select
ID, '222' as product ID, '222'n as value
from _data
where '222'n > 0 and ID > 0
union all
quit;
Due to having almost hundred product ID numbers the code ends up being very long. There must be a more convenient way to get the result I want. Is anyone able to help making my code shorter?
You seem to haven't seen PROC TRANSPOSE yet.
proc transpose
data=have
out=want (rename=(_name_=product_id col1=value))
;
by id;
var '111'n--'999'n;
run;
Replace 999 with the name of the rightmost column.
You seem to haven't seen PROC TRANSPOSE yet.
proc transpose
data=have
out=want (rename=(_name_=product_id col1=value))
;
by id;
var '111'n--'999'n;
run;
Replace 999 with the name of the rightmost column.
Note that the notation
varx--vary
grabs all variables that are physically allocated between varx and vary.
var111-var999
OTOH would look for all variables in the range, meaning 889 all in all, without regard for their location within an observation..
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.