topic ARRAY in SAS Programming

ARRAY

emelergul — Mon, 24 Apr 2023 17:51:24 GMT

Hi there- I have 4 different terms as Q1, Q2, Q3 Q4, for each subject .

ID TERMS

id1 Q1

id1 Q2

id1 Q3

id1 Q4

id2 Q1

id2 Q2

id3 Q4

I want them like:

Id Terms

id1 Q1 Q2 Q3 Q4

id2 Q1 Q2

id3 Q4

I need a basic array code, here what I drafted does not give the output I need.

data new;

set old;

array T(4) TermsQ1-TermsQ4

Do i=1 to 4;

end;

Any advice? This is a simple array but the out put is not what I wanted.

Re: ARRAY

Kurt_Bremser — Mon, 24 Apr 2023 18:04:49 GMT

proc sort
  data=have
  out=sorted
  nodupkey
;
by id terms;
run;

proc transpose
  data=sorted
  out=want (drop=_name_)
;
by id;
var terms;
run;

Untested, posted from my tablet.

Re: ARRAY

mkeintz — Mon, 24 Apr 2023 18:11:30 GMT

Before you can get an effective response to your question, you'll need to provide more information. In particular

Sample "data" ambiguity. Your description of the current data layout suggests that ID1 and ID2 have two starting values for Q1 (and ID1 has two values for Q4). Yet the desired output shows the ability to preserve only one value. What do you want to do about such cases?
What is the current storage of your current "vertical" data. Is it in a plain text file, or in a SAS data set?

If you provide actual sample data, it will be far easier to help. Your initial data should have for each "row", an ID, a designation of which Q is specified, and the actual value for that ID/Q combination.

Re: ARRAY

Tom — Mon, 24 Apr 2023 19:10:43 GMT

What do you want to do with duplicates? Do you want to ignore them?

Where do you want the values to go? If you want them to go into a variable with same name as the value then perhaps just use the LET option on PROC TRANSPOSE will work?

proc transpose data=have out=want let;
  by id;
  id term;
  var term;
run;

Otherwise eliminate the duplicates first.

proc sort nodupkey data=have out=for_transpose;
  by id term;
run;
proc transpose data=for_transpose prefix=Q out=want;
  by id;
  var term;
run;

Here is what the two different outputs look like: