topic Re: How to get data presented in columns to separate rows? in SAS Enterprise Guide

How to get data presented in columns to separate rows?

vuohi — Wed, 29 Sep 2021 05:21:59 GMT

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?

Re: How to get data presented in columns to separate rows?

Kurt_Bremser — Wed, 29 Sep 2021 05:31:39 GMT

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.

Re: How to get data presented in columns to separate rows?

vuohi — Wed, 29 Sep 2021 07:10:33 GMT

Thanks KurtBremser, this was a great solution! The only problem I encountered was that I needed to have the IDs in ascending order but it was easily fixed with proc sort.

Re: How to get data presented in columns to separate rows?

Kurt_Bremser — Wed, 29 Sep 2021 07:19:01 GMT

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