DATA Step, Macro, Functions and more

how to code this efficiently?

Reply
Super Contributor
Posts: 318

how to code this efficiently?

I have data with the right column, "CYCLE1 DAY1" etc, and want to recode into the left column. 

I am thinking about a do loop but not sure how to do it. Can anyone help? Thanks

 

1011 CYCLE1 DAY1
1012 CYCLE1 DAY8
1013 CYCLE1 DAY15
1014 CYCLE1 DAY22
1015 CYCLE1 DAY29
1016 CYCLE1 DAY36
1021 CYCLE2 DAY1
1022 CYCLE2 DAY8
1023 CYCLE2 DAY15
1024 CYCLE2 DAY22
1025 CYCLE2 DAY29
1026 CYCLE2 DAY36
1031 CYCLE3 DAY1
1032 CYCLE3 DAY8
1033 CYCLE3 DAY15
1034 CYCLE3 DAY22
1035 CYCLE3 DAY29
1041 CYCLE4 DAY1
1042 CYCLE4 DAY8
1043 CYCLE4 DAY15
1044 CYCLE4 DAY22
1045 CYCLE4 DAY29
1051 CYCLE5 DAY1
1053 CYCLE5 DAY15
1055 CYCLE5 DAY29
1051 CYCLE6 DAY1
1063 CYCLE6 DAY15
1065 CYCLE6 DAY29
1071 CYCLE7 DAY1
1073 CYCLE7 DAY15
1075 CYCLE7 DAY29
1081 CYCLE8 DAY1
1083 CYCLE8 DAY15
1085 CYCLE8 DAY29
1091 CYCLE9 DAY1
1093 CYCLE9 DAY15
1095 CYCLE9 DAY29
1101 CYCLE10 DAY1
1103 CYCLE10 DAY15
1105 CYCLE10 DAY29
Super User
Posts: 19,810

Re: how to code this efficiently?

Posted in reply to fengyuwuzu

No...because you haven't provided enough information on what you're trying to do.

Post a more detailed BUT smaller example. We only need about 5 or 6 rows to get the idea and please format it appropriately to fit in the forum.  

Super Contributor
Posts: 318

Re: how to code this efficiently?

[ Edited ]

that is all the data I have. my code is like this but there are errors. can anyone help me improve the code?

data out;
set in;
i=1;
do while (i <10);
if i=input(substr(avisit, 6, 1),1.) then do;
if 'DAY1' in avisit then avisitn='10'||put(i,1.)||'1';
if 'DAY8' in avisit then avisitn='10'||put(i,1.)||'2';
if 'DAY15' in avisit then avisitn='10'||put(i,1.)||'3';
if 'DAY22' in avisit then avisitn='10'||put(i,1.)||'4';
if 'DAY29' in avisit then avisitn='10'||put(i,1.)||'5';
if 'DAY36' in avisit then avisitn='10'||put(i,1.)||'6';
end;
i=i+1;
end;
run 
Super User
Posts: 19,810

Re: how to code this efficiently?

Posted in reply to fengyuwuzu

I don't understand what you're trying to do. Post a small example of what you have and what your trying to get based on what you have. Non working code doesn't tell us much especially if it's a logic/methodology issue.

Super Contributor
Posts: 318

Re: how to code this efficiently?

have want
CYCLE1 DAY1 1011
CYCLE1 DAY8 1012
CYCLE1 DAY15 1013
CYCLE1 DAY22 1014
CYCLE1 DAY29 1015
CYCLE1 DAY36 1016
CYCLE2 DAY1 1021
CYCLE2 DAY8 1022
CYCLE2 DAY15 1023
CYCLE2 DAY22 1024
CYCLE2 DAY29 1025
CYCLE2 DAY36 1026
CYCLE3 DAY1 1031
CYCLE3 DAY8 1032
CYCLE3 DAY15 1033
CYCLE3 DAY22 1034
CYCLE3 DAY29 1035
Super User
Posts: 19,810

Re: how to code this efficiently?

Posted in reply to fengyuwuzu

Two column headings were all you needed...

 

data have;
input string $30.;
cards;
CYCLE1 DAY1	
CYCLE1 DAY8	
CYCLE1 DAY15	
CYCLE1 DAY22	
CYCLE1 DAY29	
CYCLE1 DAY36
CYCLE2 DAY1	
CYCLE2 DAY8	
CYCLE2 DAY15	
CYCLE2 DAY22	
CYCLE2 DAY29	
CYCLE2 DAY36	
CYCLE3 DAY1	
CYCLE3 DAY8	
CYCLE3 DAY15	
CYCLE3 DAY22	
CYCLE3 DAY29
;
run;

data have2;
set have;

cycle=substr(scan(string, 1), 6);
day = substr(scan(string, 2), 4);

run;

proc sort data=have2;
by cycle day;
run;

data want;
set have2;
by cycle;
if first.cycle then Index=input(cycle, 8.)*10 + 1;
else index+1;

want_field = 1000+index;

drop cycle day index;

run;	
Super Contributor
Posts: 318

Re: how to code this efficiently?

Thank you. I got your idea. Right now the order is wrong because DAY8 was ranked the last, but I will try to figure it out.

 

Super User
Posts: 19,810

Re: how to code this efficiently?

Posted in reply to fengyuwuzu

 I didn't convert cycle/day to numbers so they're not sorting correctly. Convert them to numeric variables or look at the different sort options so they'll sort correctly and then the rest of the code will work. 

 

Spoiler
data have;
input string $30.;
cards;
CYCLE1 DAY1
CYCLE1 DAY8
CYCLE1 DAY15
CYCLE1 DAY22
CYCLE1 DAY29
CYCLE1 DAY36
CYCLE2 DAY1
CYCLE2 DAY8
CYCLE2 DAY15
CYCLE2 DAY22
CYCLE2 DAY29
CYCLE2 DAY36
CYCLE3 DAY1
CYCLE3 DAY8
CYCLE3 DAY15
CYCLE3 DAY22
CYCLE3 DAY29
;
run;

data have2;
set have;

cycle=input(substr(scan(string, 1), 6), 8.);
day = input(substr(scan(string, 2), 4), 8.);

run;

proc sort data=have2;
by cycle day;
run;

data want;
set have2;
by cycle;
if first.cycle then Index=cycle*10 + 1;
else index+1;

want_field = 1000+index;

drop cycle day index;

run;
Super Contributor
Posts: 275

Re: how to code this efficiently?

Posted in reply to fengyuwuzu

data have;
input x &$30.;
_x=cats('10',compress(scan(x,1),,'kd'), (round(compress(scan(x,2),,'kd')/7)+1));
cards;
CYCLE4 DAY1
CYCLE4 DAY8
CYCLE4 DAY15
CYCLE4 DAY22
CYCLE4 DAY29
CYCLE5 DAY1
CYCLE5 DAY15
CYCLE5 DAY29
CYCLE6 DAY1
;
proc print;
run;

Super User
Posts: 11,343

Re: how to code this efficiently?

[ Edited ]
Posted in reply to fengyuwuzu

fengyuwuzu wrote:

that is all the data I have. my code is like this but there are errors. can anyone help me improve the code?


I believe that we have mentioned more than once that you should include the log of the code with the error messages.

 

The IN operator in SAS currently does not support a variable as the target but only literal values. And IN will not do substringing at all.

To search for a value within a string use either INDEX if the target may not be considered a word or FINDW

Ask a Question
Discussion stats
  • 9 replies
  • 420 views
  • 0 likes
  • 4 in conversation