turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Using transpose to divide 2 variables into multipl...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-05-2018 01:00 AM

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 (**time**s 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

Accepted Solutions

Solution

02-05-2018
10:57 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to error_prone

02-05-2018 10:54 AM

**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;

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to aespinarey

02-05-2018 03:00 AM

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.

Solution

02-05-2018
10:57 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to error_prone

02-05-2018 10:54 AM

**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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to aespinarey

02-05-2018 10:59 AM

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