BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pmesh
Calcite | Level 5

Hello,

The data I have is as follows:

IDScore1 Score2Score3
A528
B437
C961

I want to convert it as follows:

IDScore
A5
B4
C9
A2
B3
C6
A8
B7
C1

The way I'm doing it pretty crude:

data want;

     set have (keep = id score1 rename=(score1=score)

          have (keep = id score2 rename=(score2=score)

          have (keep = id score3 rename=(score3=score);


run;


This could be tedious with say 50 variables. Is there any sophisticated way? Can proc transpose handle this?


Thanks,

P

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Then do the following:

data want (keep=id score);

set have;

array scores {} score1-score3; * or use _numeric_ if all numeric variables are to be taken for output;

do i = 1 to dim(scores);

  score = scores{i};

  output;

end;

run;

View solution in original post

6 REPLIES 6
Kurt_Bremser
Super User

One option would be to create the list of input datasets automatically with a macro loop. proc transpose would (IMHO) not be able to create your A,B,C,A,B,C order. It would rather give you A,A,A,B,B,B,C,C,C instead.

If the order of output records doe not matter, usage of an array and looping through that would also come to mind.

pmesh
Calcite | Level 5

Thanks for your reply. No, order is not important here.

Kurt_Bremser
Super User

Then do the following:

data want (keep=id score);

set have;

array scores {} score1-score3; * or use _numeric_ if all numeric variables are to be taken for output;

do i = 1 to dim(scores);

  score = scores{i};

  output;

end;

run;

pmesh
Calcite | Level 5

Superb. Worked perfectly!

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

data have;

  infile datalines;

  input ID $ Score1 Score2 Score3;

datalines;

A 5 2 8

B 4 3 7

C 9 6 1

;

run;

data _null_;

  set sashelp.vcolumn (where=(libname="WORK" and memname="HAVE" and substr(name,1,5)="Score")) end=last;

  if _n_=1 then call execute('data want; set ');

  call execute(' work.have (keep=id '||strip(name)||' rename=('||strip(name)||'=Score))');

  if last then call execute(';run;');

run;

Community_Help
SAS Employee

Hi - thanks for being part of Communities on SAS - I noticed this question was asked in the "about communities on SAS" community .. just for greater visibility I'm moving it to the SAS procedures community. Best regards!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1039 views
  • 5 likes
  • 4 in conversation