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?
... View more
