Transposing data

[Abimal_Zippi]

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,

[Kurt_Bremser]

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.

[Abimal_Zippi]

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,

[Kurt_Bremser]

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  

[Abimal_Zippi]

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

[Kurt_Bremser]

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;