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,
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.
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,
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
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.