Help using Base SAS procedures

proc transpose with LET

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

proc transpose with LET

Hello,

I have a dataset with repeating IDs and I want to keep them repeating under a certain condition.

So my table looks like this:

SAMPLEIDResponseNumberItemAmountKcal.
11A55100
11B66200
11C77300
12A55150
12B66250
12C77350
12D88450
21A3360
21B4470
21C5580
22A2250
22B33100
22C44150

And I would like it to be like this:

SAMPLEIDResponseNumberItemAitemBItemCItemDKcal
115566770600
12556677881200
213344550210
222233440300
etc...

I used

proc freq data=old;

tables SAMPLEID*Item out=new;

weight Amount / zeros;

run;

(I used proc freq so that if a respondent got the same Item a few times, I get a sum of its amount.)

proc transpose data=new prefix=item out=transposed;

by SAMPLEID;

id Amount;

var count;

run;

What i got was transposed table that summarized all amounts for each item and dropped KCAL.. I think it gets summarized at the point of proc freq... that's why using LET after proc transpose will not change anything i guess..

What should I add/change to have the IDs repeating if ResponseNumber changes from 1 to 2..? Is there any way to mark/rename all the repeating IDs ?.. E.g. If ResponseNumber = 1 then ID changes from 1 to 1-1 , from 2 to 2-2 and so on... and if ResponseNumber = 2, then 1-2, 2-2, 3-2 and so on..

Thank you very much!

Best,

Anastasia


Accepted Solutions
Solution
‎01-22-2015 09:05 PM
Super User
Super User
Posts: 6,498

Re: proc transpose with LET

Why not just do the SUM and the TRANSPOSE in two different steps and merge the results?

data have ;

  input SAMPLEID ResponseNumber Item $ Amount Kcal;

cards;

1 1 A 55 100

1 1 B 66 200

1 1 C 77 300

1 2 A 55 150

1 2 B 66 250

1 2 C 77 350

1 2 D 88 450

2 1 A 33 60

2 1 B 44 70

2 1 C 55 80

2 2 A 22 50

2 2 B 33 100

2 2 C 44 150

;;;;

proc transpose data=have prefix=item out = t1 ;

  by sampleid responsenumber ;

  id item ;

  var amount ;

run;

proc summary data=have ;

  by sampleid responsenumber ;

  var kcal ;

  output out=t2 sum=kcal ;

run;

data want ;

  merge t1 t2 ;

  drop _name_ _freq_ _type_;

  by sampleid responsenumber ;

run;

View solution in original post


All Replies
Super User
Posts: 17,775

Re: proc transpose with LET

Add responseNumber into your BY Statement.

proc transpose data=new prefix=item out=transposed;

by SAMPLEID ResponseNumber;

id Amount;

var count;

run;

Since its not quite a 'straight' transpose you may want to transpose using a data step instead:

SAS Learning Module: Reshaping data long to wide using the data step

Contributor
Posts: 52

Re: proc transpose with LET

a solution:

proc transpose data=t_a prefix=item out=t_b1;
by SAMPLEID ResponseNumber;
id item;
var amount;
run;

data t_b2(keep= SAMPLEID ResponseNumber zSum rename=(zSum=Kcal));
  set t_a;
  by SAMPLEID ResponseNumber;
  if first.ResponseNumber then zSum=0;
  zSum+kcal;
  if last.ResponseNumber then output;
run;

/** if you want the null values to become zeros', use coalesce() function **/

data t_want(drop=i _name_);
   merge t_b1(in=a) t_b2(in=b);
   by SAMPLEID ResponseNumber;
   ARRAY NN(*) _NUMERIC_;
   DO I = 1 TO DIM(NN);
      NN(I) = COALESCE(NN(I),0);
   END;
run;

Solution
‎01-22-2015 09:05 PM
Super User
Super User
Posts: 6,498

Re: proc transpose with LET

Why not just do the SUM and the TRANSPOSE in two different steps and merge the results?

data have ;

  input SAMPLEID ResponseNumber Item $ Amount Kcal;

cards;

1 1 A 55 100

1 1 B 66 200

1 1 C 77 300

1 2 A 55 150

1 2 B 66 250

1 2 C 77 350

1 2 D 88 450

2 1 A 33 60

2 1 B 44 70

2 1 C 55 80

2 2 A 22 50

2 2 B 33 100

2 2 C 44 150

;;;;

proc transpose data=have prefix=item out = t1 ;

  by sampleid responsenumber ;

  id item ;

  var amount ;

run;

proc summary data=have ;

  by sampleid responsenumber ;

  var kcal ;

  output out=t2 sum=kcal ;

run;

data want ;

  merge t1 t2 ;

  drop _name_ _freq_ _type_;

  by sampleid responsenumber ;

run;

Super Contributor
Posts: 336

Re: proc transpose with LET

Maybe a solution without transpose is worth a thought:

data have ;
  input SAMPLEID ResponseNumber Item $ Amount Kcal;
  cards;
1 1 A 55 100
1 1 B 66 200
1 1 C 77 300
1 2 A 55 150
1 2 B 66 250
1 2 C 77 350
1 2 D 88 450
2 1 A 33 60
2 1 B 44 70
2 1 C 55 80
2 2 A 22 50
2 2 B 33 100
2 2 C 44 150
;;;;

Data Want (Keep=Item: Total SAMPLEID ResponseNumber) ;
  Array Arr[4] ItemA ItemB ItemC ItemD;
  Do i=1 By 1 Until (Last.ResponseNumber);
    Set have;
By SAMPLEID ResponseNumber;
Arr=Amount;
  End;
  Do j=1 To i;
    Set Have;
By SAMPLEID ResponseNumber;
Total=Sum(Kcal,Total);
  End;
Run;

Occasional Contributor
Posts: 13

Re: proc transpose with LET

Thanks everyone! Tom's code worked with a few modifications.. I had to use proc sort to make sure the program can read all the entries, proc summary (for items & amounts and another one for items & kcal), merge two summaries and then transpose!

Thanks a lot!

Occasional Contributor
Posts: 13

Re: proc transpose with LET

Tom, thank you very much!

Is there any way to mark/rename all the repeating IDs ?.. E.g. If ResponseNumber = 2 then ID changes from 1 to 1_2 and so on... ?

Thanks,

Anastasia

Super User
Super User
Posts: 6,498

Re: proc transpose with LET

Do you mean just use both variables when generating the column name?

proc transpose data=have prefix=Response_ delim=_ out = t1 ;

  by sampleid ;

  id item responsenumber ;

  var amount ;

run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 454 views
  • 7 likes
  • 5 in conversation