topic Re: Transposing data in SAS Programming

Transposing data

Abimal_Zippi — Fri, 05 Jul 2019 04:52:20 GMT

I have tried the following program but not working as desired:

Sample data structure and desired form attached

Proc transpose data=X out Y;

By STRATA ID;

Id ITEM_CODE;

VAR ITEM_NAME;

run;

Thanks a lot,

Re: Transposing data

Kurt_Bremser — Fri, 05 Jul 2019 05:07:54 GMT

You're missing an equals sign.

Proc transpose data=X out=Y; /* out also needs a = */
By STRATA ID;
Id ITEM_CODE;
VAR ITEM_NAME;
run;

Please provide example data in a data step with datalines; post the code into a window opened with the "little running man".

No need for attaching Office documents, which are blocked from downloading at many corporate sites.

Re: Transposing data

Abimal_Zippi — Mon, 08 Jul 2019 02:01:41 GMT

Thanks, I missed "equals" sign, while posting the code.

Here is the data step with data lines,

Data X;

Input ID STRATA ITEM_NAME $ ITEM_CDE $;

Datalines;

 

1 1 Pen 101A

1 1 Paper 102A

1 1 Printer 103A

1 3 Pen 101A

1 3 Paper 102A

1 3 Printer 103A

1 4 Pencil 104A

2 2 Pen 101A

2 2 Paper 102A

2 2 Printer 103A

2 9 Pencil 104A

;

*Here is the data structure desired;

data desired;

Input ID STRATA ITEM_NAME $ ITEM_CDE $;

Datalines;

1 1 1 0 1 1

1 3 1 0 1 1

1 4 0 1 0 0

2 2 1 0 1 1

2 9 0 1 0 0

;

Thanks a lot,

Re: Transposing data

Kurt_Bremser — Mon, 08 Jul 2019 08:04:40 GMT

I can't make sense of your "desired" dataset, you don't have any variable names that could be the result of a transpose.

See this code for a basic transpose:

data have;
input id strata item_name $ item_cde $;
item = 1;
datalines;
1 1 Pen 101A
1 1 Paper 102A
1 1 Printer 103A
1 3 Pen 101A
1 3 Paper 102A
1 3 Printer 103A
1 4 Pencil 104A
2 2 Pen 101A
2 2 Paper 102A
2 2 Printer 103A
2 9 Pencil 104A
;

proc transpose
  data=have
  out=trans (drop=_name_)
  prefix=_
;
by id strata;
id item_cde;
var item;
run;

data want;
set trans;
array nums {*} _:;
do i = 1 to dim(nums);
  nums{i} = max(0,nums{i});
end;
drop i;

proc print data=want noobs;
run;

Note that I added a numeric variable to get the numeric values in the result.

The intermediate step is for replacing missings with zeroes.

Result:

id    strata    _101A    _102A    _103A    _104A

 1       1        1        1        1        0  
 1       3        1        1        1        0  
 1       4        0        0        0        1  
 2       2        1        1        1        0  
 2       9        0        0        0        1

Re: Transposing data

Abimal_Zippi — Wed, 10 Jul 2019 01:55:20 GMT

Sorry for the confusion, here is my updated desired outcome;

Strata id Pen Pencil Printer Paper

1 1 101A . 103A 102A

1 3 101A . 103A 102A

1 4 . 104A . .

2 2 101A 104A 103A 102A

2 9 . 104A . .

3 3 101A 104A 103A 102A

Re: Transposing data

Kurt_Bremser — Wed, 10 Jul 2019 05:32:58 GMT

I guess you mistakenly changed id and strata. This should do it:

proc transpose data=x out=y;
by id strata;
id item_name;
var item_cde;
run;