topic Re: Using transpose to divide 2 variables into multiple records each in SAS Programming

Using transpose to divide 2 variables into multiple records each

aespinarey — Mon, 05 Feb 2018 06:00:47 GMT

Hello all,

I am having trouble using the proc transpose statement to arrange the data set to have only one observation per subject and break down 2 variables into 13 records each. There are 7 subjects and 13 different observations for each subject (times and concentrations). Below is my program, my variables, and the log error message.

Variables: Obs subject Time Concentration >>>proc transpose>>> Obs Subject T1 T2 T3 T4 T5 T6 T7 T8 T9 T10 T11 T12 T13 C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12 C13

data results;
n = 0;
do until (last.subject);
set PROJECT2_F17;
by subject concentration;
if first.subject then first_time = concentration;
end;

if n = 0 then T1= time;
if n=0.5 then T2= time;
if n = 1 then T3= time;
if n=1.5 then T4= time;
if n = 2 then T5= time;
if n=3 then T6= time;
if n = 4 then T7= time;
if n=6 then T8= time;
if n = 8 then T9= time;
if n=12 then T10= time;
if n = 16 then T11= time;
if n=24 then T12= time;
else T13= time;
drop n time;
run;
if n = 0 then C1= concentration;
if n=0.5 then C2= concentration;
if n = 1 then C3= concentration;
if n=1.5 then C4= concentration;
if n = 2 then C5= concentration;
if n=3 then C6= concentration;
if n = 4 then C7= concentration;
if n=6 then C8= concentration;
if n = 8 then C9= concentration;
if n=12 then C10= concentration;
if n = 16 then C11= concentration;
if n=24 then C12= concentration;
else C13= concentration;
drop n concentration;
run;
proc print data=results;
run;

LOG:

ERROR: BY variables are not properly sorted on data set WORK.PROJECT2_F17.
n=0 last.subject=0 subject=1 Time=3 Concentration=4.7 FIRST.subject=0 FIRST.Concentration=0
LAST.Concentration=1 first_time=0 T1=. T2=. T3=. T4=. T5=. T6=. T7=. T8=. T9=. T10=. T11=. T12=.
T13=. _ERROR_=1 _N_=1
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 7 observations read from the data set WORK.PROJECT2_F17.
WARNING: The data set WORK.RESULTS may be incomplete. When this step was stopped there were 0
observations and 16 variables.
WARNING: Data set WORK.RESULTS was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

108 proc print data=results;
109 run;

NOTE: No observations in data set WORK.RESULTS.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Re: Using transpose to divide 2 variables into multiple records each

error_prone — Mon, 05 Feb 2018 08:00:33 GMT

Please post example input data as data-step. When using BY in data-step you need to sort the data before. Later on in your code some if statements appear outside of a data-step. You need to fix this too.

Re: Using transpose to divide 2 variables into multiple records each

aespinarey — Mon, 05 Feb 2018 15:54:56 GMT

After transposed and mergedData input has 7 subjects and 91 observations (only part of it is here)

This program is working now, I am having dropping the _label_ and _NAME_ though.

*****IMPORT using macro code;
%macro P2 (a, b, c);
proc import out= &a
datafile= "C:\Project_2\&b"
dbms=xlsx replace;
getnames=yes;
run;
proc sort data=&a;
by &c;
run;
Proc print data = &a;
Run;
%mend P2;

%P2 (PROJECT2_F17, Project2.xlsx, subject);
****Restructure data using Proc transpose;

PROC TRANSPOSE DATA=PROJECT2_F17 OUT=P2times (DROP= _NAME_ _LABEL_) PREFIX=T;
BY subject;
VAR time ;
RUN;
PROC TRANSPOSE DATA= PROJECT2_F17 OUT=P2conc (DROP= _NAME_ _LABEL_) PREFIX=C;
BY subject ;
VAR concentration ;
RUN;
***MERGE transposed sets;
DATA Project2_TC;
merge P2times P2conc;
by subject;
run;
proc print data=Project2_TC;
run;

Re: Using transpose to divide 2 variables into multiple records each

aespinarey — Mon, 05 Feb 2018 15:59:33 GMT

It worked once i ran the entire program together. _LABEL_ and _NAME_ were dropped.