Transposing multiple variables

[claremc]

Hello, 

I am trying to transpose multiple variables (Result and CollectedDate below) while also keeping some demographic variables as constants from the original dataset. Basically, what is happening with the following code is that the variables are transposing but each on their own line. So the "ab_case_id" variable is still appearing as 2 rows instead of one (1 row for each transposed variable). Also, none of the demographic variables from the original dataset (abdup_check_minusdups) are coming through into the output dataset. What am I doing wrong? 

proc transpose data = abdup_check_minusdups 
			   out = abdup_check2
			   prefix = Result ;
			   by ab_case_id; 
			   id neg_test;
			   var Result CollectedDate;
run; 

Thanks!

Clare

[ballardw]

Example of the input data.

Example of the desired data after the transpose.

These go much better than trying to figure out what you mean by this row, that row, and such.

If you want anything resembling tested code we need data that looks like yours.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

Any variable not explicitly used in a Proc Transpose statement will be dropped.

You may get some additional information by using the other variables on a COPY statement but sometimes the results aren't what you want.

If the set of variables are all demographics and don't change for the AB_case_id  you might consider using all of them on the BY statement but that might require an additional sort.

[claremc]

Hi, thanks for your response -- 

See below -- 

/*1*/ is how my data looks.

/*2*/ is how my data appears after the transpose code I posted below 

/*3*/ is how I want my data to appear

/*1*/
 data WORK.CLASS(label='abdup_check_minusdups');
   infile datalines dsd truncover;
   input ab_case_id:$8. dob mmdddy10. Result:$8.  CollDat:8.;
 datalines;
 01234 Result1 02/07/2008 CollDat1
 01234 Result2 02/07/2008 CollDat2
 ;;;;
run; 
/*2*/
 data WORK.CLASS(label='abdup_check_minusdups');
   infile datalines dsd truncover;
   input ab_case_id:$8. dob mmdddy10. Result:$8.  CollDat:8.;
 datalines;
 01234 Result1 Result2
 01234 Colldat1 CollDat2
 ;;;;
run; 
/*3*/
 data WORK.CLASS(label='abdup_check_minusdups');
   infile datalines dsd truncover;
   input ab_case_id:$8. dob mmdddy10. Result:$8.  CollDat:8.;
 datalines;
 01234 Result1 02/07/2008 CollDat1 Result2 CollDat2
 ;;;;
run; 

Sidenote -- the format data example code you posted with the github macro did not work for me. 

[PhilC · Accepted solution]

It is not one transpose, but two:

data Have(sortedby=ab_case_id dob);
  infile datalines  dlm=" ";
  input ab_case_id:$8. Result:$7. dob mmddyy10.  CollDat:$8.;
    format dob mmddyy10.;
datalines;
01234 Result1 02/07/2008 CollDat1
01234 Result2 02/07/2008 CollDat2
 ;;;;
run; 
PROC TRANSPOSE DATA=Have
	OUT=WORK.Result(LABEL="Transposed WORK.WANT" drop=_NAME_)
	PREFIX=Result
;
	BY dob ab_case_id;
	VAR Result;

RUN; QUIT;
PROC TRANSPOSE DATA=Have
	OUT=WORK.Coldat(LABEL="Transposed WORK.WANT" drop=_NAME_)
	PREFIX=Coldat
;
	BY dob ab_case_id;
	VAR CollDat;
RUN; QUIT;
data want_ish;
  merge Result Coldat;
    by ab_case_id dob;
run;

[claremc]

Thank you!!!!