topic Obtaining a row of key from a transpose file in SAS Programming

Obtaining a row of key from a transpose file

lapetitemaman — Mon, 07 Mar 2022 15:38:27 GMT

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.

Re: Obtaining a row of key from a transpose file

lapetitemaman — Mon, 07 Mar 2022 15:39:38 GMT

Sorry, Item789 should be Item789 $.

Re: Obtaining a row of key from a transpose file

Tom — Mon, 07 Mar 2022 16:27:42 GMT

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?

Re: Obtaining a row of key from a transpose file

lapetitemaman — Mon, 07 Mar 2022 16:41:43 GMT

The example file is what I have obtained after transposing another file. There is no transposing here. The values A, B, C, D, and 1 are the correct answers (or key) and each ID were administered different items. The values I want are only one row of values (with no missing value) that I can use as the key file for another program. I can change and rename the first variable after I obtain one row of values.

Re: Obtaining a row of key from a transpose file

Tom — Mon, 07 Mar 2022 17:00:45 GMT

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;

Re: Obtaining a row of key from a transpose file

lapetitemaman — Mon, 07 Mar 2022 18:25:04 GMT

The reason I used transpose to get this file is because I had a transposed response file and these two files need to match (in terms of item order, examinee ID order). I wish the item order followed a constant value... Thank you for the help.