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

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

 

 
1 ACCEPTED SOLUTION

Accepted Solutions
aespinarey
Obsidian | Level 7

After transposed and mergedAfter transposed and mergedData input has 7 subjects and 91 observations (only part of it is here)Data 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;

View solution in original post

3 REPLIES 3
error_prone
Barite | Level 11
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.
aespinarey
Obsidian | Level 7

After transposed and mergedAfter transposed and mergedData input has 7 subjects and 91 observations (only part of it is here)Data 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;

aespinarey
Obsidian | Level 7

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 869 views
  • 1 like
  • 2 in conversation