Greetings everyone,
I got this file after transpose but only need to keep one row of key. I know proc sort can delete duplicates but I need the items stay the same order.
data example;
input ID $ Item105 $ Item124 $ Item 992 $ Item020 Item 035 $ Item 021 Item025 Item789;
datalines;
001 A B . 1 D . 1 A
002 . B C 1 . 1 . A
003 . . C 1 D 1 1 A
004 A . . 1 D . 1 A
005 . . C . D 1 1 .
006 A B . . D 1 . A
007 A B . 1 . 1 . A
008 . . C 1 D . . A
009 A . C . . 1 . .
010 . . . 1 D 1 1 A
011 . B C . D 1 . A
;
data want;
input ID $ Item105 $ Item124 $ Item 992 $ Item020 Item 035 $ Item 021 Item025 Item789;
datalines;
001 A B C 1 D 1 1 A
;
How will I be able to achieve this? Thank you so much in advance.
Wouldn't it be easier to get the answer key from the original dataset rather than after you have transposed it? Probably a simple select distinct query in SQL or a PROC SORT NODUPKEY step.
You could trick the UPDATE function into doing what you want. But you will need some constant value variable to use as the BY variable.
data step1;
set example;
if _n_=1 then dummy=id;
retain dummy;
run;
data want;
update step1(obs=0) step1;
by dummy;
run;
I don't see any transpose here or how transpose would have anything to do with this.
Can you explain the reason you get that value as the output?
Why does ID='001'? What happens when ITEM105 has values other than 'A' or ' '? Which value would you want?
Wouldn't it be easier to get the answer key from the original dataset rather than after you have transposed it? Probably a simple select distinct query in SQL or a PROC SORT NODUPKEY step.
You could trick the UPDATE function into doing what you want. But you will need some constant value variable to use as the BY variable.
data step1;
set example;
if _n_=1 then dummy=id;
retain dummy;
run;
data want;
update step1(obs=0) step1;
by dummy;
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.