topic Re: how to code this efficiently? in SAS Programming

how to code this efficiently?

fengyuwuzu — Fri, 14 Oct 2016 17:30:18 GMT

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

Re: how to code this efficiently?

Reeza — Fri, 14 Oct 2016 17:54:12 GMT

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.

Re: how to code this efficiently?

fengyuwuzu — Fri, 14 Oct 2016 18:21:33 GMT

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

Re: how to code this efficiently?

Reeza — Fri, 14 Oct 2016 18:28:10 GMT

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.

Re: how to code this efficiently?

fengyuwuzu — Fri, 14 Oct 2016 18:39:07 GMT

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

Re: how to code this efficiently?

Reeza — Fri, 14 Oct 2016 19:12:19 GMT

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;

Re: how to code this efficiently?

slchen — Fri, 14 Oct 2016 19:19:36 GMT

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;

Re: how to code this efficiently?

fengyuwuzu — Fri, 14 Oct 2016 19:26:53 GMT

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.

Re: how to code this efficiently?

ballardw — Fri, 14 Oct 2016 19:41:43 GMT

@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

Re: how to code this efficiently?

Reeza — Fri, 14 Oct 2016 19:33:49 GMT

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.

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;