topic Double proc transpose help in SAS Programming

Double proc transpose help

Mscarboncopy — Mon, 15 Feb 2021 22:12:26 GMT

Hi. I have a variable that has correspondent dates and I need to use a double proc transpose.

However, my first transpose is simply to transpose the variable main from wide to long, and if I do it this way all the dates are in the first row and not in the rows they should be.

This is the code

proc transpose data=have out=long ( where=(col1 ne 0) ) ; by obs; var main:; run; data want; set long; MAIN = scan(_name_,-1,"_"); drop _name_ col1; run;

When I look at the data with the main var transpose this is what I have so now I would need a main variable for the Date.

ID MAIN date1 date2 date3 date4 date5 etc (I have 16 dates that match keys 1-16 in main var)

A     1               date    .        .       date      date          all else missing
A     4                .         .        .            .         .              all else missing
A     5                .         .        .           .           .          all else missing

B    1                 date   .        .           .        date        all else missing
B    5                 .         .        .           .        .      all else missing
C    3                 .         .       date      .        .          all else missing

So this code below does not work because all the dates are in the first row (due to the first transpose) in the example below for ID A the first row is MAIN 1.

proc transpose data=have
out=want;
by ID MAIN;
var date1-date16;
Run;

is giving me this:

ID MAIN NAME COLUMN 1 (My goal is to rename this maindate)

A 1 date1 date 1 here

A 1 date2 .
A 1 date 3 .

A 1 date4 date 4 here (this should be for MAIN 4 not 1)
A 1 date5 date 5 here (this should be for MAIN 5 not 1)

+ 13 more lines with all dates missing for Main key 1! And all missing for all the other keys (2 to 16 in Main)

The same happens for all the other Ids.

I would appreciate any help you could give me as to how I can make this double transpose work. It looks like I can't transpose main first then transpose the dates, it has to be together and I have not been able to find out how to do that.

Thanks

Re: Double proc transpose help

ChrisNZ — Tue, 16 Feb 2021 00:48:59 GMT

Please provide a short set of data as a data step for what you have and what you want.

Re: Double proc transpose help

Mscarboncopy — Tue, 16 Feb 2021 15:19:31 GMT

proc transpose
data=have
out=long (
where=(col1 ne 0)
)
;
by ID;
var main:; * 16 vars main_1 main_2 etc became 16 keys in var main;
run;

data want;
set long;
Main = scan(_name_,-1,"_");
drop _name_ col1 col2;
run;

I then merge this data file with the dates from the original data file where the dates are (I call this merge Final) making sure to delete any main that is missing as I don't want those in.

Then I thought I could do this:

proc transpose data=Final
out=wantfinal;
by id main;
var date1-date16;
Run;

I think I need to have this all in one step? Transpose main and dates all at once? not in so many steps. Maybe it is the reason why I am encountering the issue I described.

I am not sure if I can give you an input statement if that is what you needed. Hopefully this helps.

What I need is a file that has only 2 variables aside from id:

1) Main (that comes from 16 main vars) and 2) Date (that comes from the matching 16 dates). There are a lot of missing, as not all 16 main variables are selected per id and only the selected main has corresponding dates.

Thanks.