topic Re: Transpose particular columns in SAS Enterprise Guide

Transpose particular columns

shubham_d — Wed, 14 Jun 2023 12:35:31 GMT

Have -

id	num1	num2	num3
1	101	102	103

data d1;

input id num1 num2 num3;

datalines;

1 101 102 103

;

run;

Want -

id	num_list
1	101
1	102
1	103

Incase either of num1, num2, num3 is null then I don't want their entries in the dataset. For example - If num3 is null then dataset should be -

id	num_list
1	101
1	102

Thanks in advance 🙂

Re: Transpose particular columns

PaigeMiller — Wed, 14 Jun 2023 12:32:33 GMT

Easiest thing to do is simply delete the unwanted rows from the output.

Re: Transpose particular columns

shubham_d — Wed, 14 Jun 2023 13:27:41 GMT

Hey! Deleting unwanted rows is fine. But how would I transpose keeping the ID same & creating a new column for this transpose? Basically the dataset of Want

Re: Transpose particular columns

PaigeMiller — Wed, 14 Jun 2023 13:57:20 GMT

Run PROC TRANSPOSE with the following in the code

by id;

Re: Transpose particular columns

Tom — Wed, 14 Jun 2023 14:04:20 GMT

@shubham_d wrote:
Hey! Deleting unwanted rows is fine. But how would I transpose keeping the ID same & creating a new column for this transpose? Basically the dataset of Want

You probably need to make more test data that actually demonstrates the issues you are having (or are worried about).

If you want the re-transposed data to end up in the same variable names then you need to either keep the _NAME_ variable that PROC TRANSPOSE generates (or something that can be used to re-create it) to use in the ID statement of PROC TRANSPOSE.

proc transpose data=have out=TALL(where=(not missing(col1)) ;
   by id;
   var var1-var3 ;
run;
proc tranpose data=TALL out=WIDE(drop=_name_);
  by id;
  id _name_;
  var col1;
run;