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 |
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.
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
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.
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 |
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;
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.
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 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;
@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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.