Turn on suggestions

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

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Using transpose to divide 2 variables into multiple records each

Options

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

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 02-05-2018 01:00 AM
(993 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

**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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. **Registration is now open through August 30th**. Visit the SAS Hackathon homepage.

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.