Hi,
I am trying to resolve below condition. But the values are not resolving.
Can you please help me?
IF MAX_NEW_Sequence_Number >= 100, then
NEW_Sequence_Number between 0 and 99, populate '01' in NEW_Provider_Number_Suffix
IF NEW_Sequence_Number >=100 then,
populate '02' in NEW_Provider_Number_Suffix and add 1 to NEW_Sequence_Number.
SAS CODE:
data test2;
set test1;
if MAX_NEW_SEQ >=100 then do;
if NEW_LOC_SEQ >=0 and NEW_LOC_SEQ =< 99 then NEW_PROVIDER_NUMBER_SUFFIX="01";
end;
if NEW_LOC_SEQ >=100 then do;
NEW_PROVIDER_NUMBER_SUFFIX='02';
NEW_LOC_SEQ_N=input(NEW_LOC_SEQ,8.) +1;
end;
run;
So something like:
data test; input new_provider_number $ new_provider_number_suffix $ new_loc_seq max_new_seq; cards; 003219469 00 4 4 003219469 00 3 14 003219469 00 2 4 003220507 00 98 105 003220507 00 99 105 003262020 00 100 100 003220507 00 98 105 003220507 00 99 105 ; run; data want; set test; retain new_sequence_number 0; if 0 <= new_loc_seq <= 99 then new_provider_number_suffix="01"; else do; new_provider_number_suffix="02"; new_sequence_number=sum(new_sequence_number,1); end; run;
What values are not resolving? Please post the log.
Also, if you want to help us help you, post some example data for us to test.
1 The SAS System Monday, December 10, 2018 01:27:00 AM 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='PRACTITIONER_CROSSWALK_0612_'; 4 %LET _CLIENTPROJECTPATH=''; 5 %LET _CLIENTPROJECTNAME=''; 6 %LET _SASPROGRAMFILE=; 7 8 ODS _ALL_ CLOSE; 9 OPTIONS DEV=ACTIVEX; 10 GOPTIONS XPIXELS=0 YPIXELS=0; 11 FILENAME EGSR TEMP; 12 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 13 STYLE=HtmlBlue 14 STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css") 15 NOGTITLE 16 NOGFOOTNOTE 17 GPATH=&sasworklocation 18 ENCODING=UTF8 19 options(rolap="on") 20 ; NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 21 22 GOPTIONS ACCESSIBLE; 23 proc sql; 24 create table test as 25 select *,max(NEW_LOC_SEQ) as MAX_NEW_SEQ from PRACTITIONER_CROSSWALK_ 26 group by NEW_PROVIDER_NUMBER; NOTE: The query requires remerging summary statistics back with the original data. NOTE: Table WORK.TEST created, with 1747357 rows and 14 columns. 27 quit; NOTE: PROCEDURE SQL used (Total process time): real time 3.35 seconds cpu time 2.11 seconds 28 29 proc sql; 30 create table test1 as 31 select NEW_PROVIDER_NUMBER,NEW_PROVIDER_NUMBER_SUFFIX,NEW_LOC_SEQ,MAX_NEW_SEQ from test; NOTE: Table WORK.TEST1 created, with 1747357 rows and 4 columns. 32 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.24 seconds cpu time 0.22 seconds 33 34 data test2; 35 set test1; 36 if MAX_NEW_SEQ >=100 then do; 37 if NEW_LOC_SEQ >=0 and NEW_LOC_SEQ =< 99 then NEW_PROVIDER_NUMBER_SUFFIX="01"; 38 end; 39 if NEW_LOC_SEQ >=100 then do; 40 NEW_PROVIDER_NUMBER_SUFFIX='02'; 41 NEW_LOC_SEQ_N=input(NEW_LOC_SEQ,8.) +1; 42 end; 2 The SAS System Monday, December 10, 2018 01:27:00 AM 43 run; NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 41:21 NOTE: Missing values were generated as a result of performing an operation on missing values. Each place is given by: (Number of times) at (Line):(Column). 12893 at 41:37 NOTE: There were 1747357 observations read from the data set WORK.TEST1. NOTE: The data set WORK.TEST2 has 1747357 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.19 seconds cpu time 0.15 seconds 44 45 GOPTIONS NOACCESSIBLE; 46 %LET _CLIENTTASKLABEL=; 47 %LET _CLIENTPROJECTPATH=; 48 %LET _CLIENTPROJECTNAME=; 49 %LET _SASPROGRAMFILE=; 50 51 ;*';*";*/;quit;run; 52 ODS _ALL_ CLOSE; 53 54 55 QUIT; RUN; 56
It looks like your not retaining any value, hence the next value is missing. That is what it is saying in the log, missing values created.
What I would suggest is thast you follow the guidance for posting a question. Start by posting test data in the form of a datastep using the code window - its the {i} above post area:
Then show what you want to see out from that test data. This avoids us having to do this back and forth trying to guess and gives us something to work with.
I am guessing that you just want to prefix a vaiable with 01 for first hundred, 02 for next etc. If so maybe something like;
data want; set have; retain cnt; if mod(_n_,100)=0 then cnt=sum(cnt,1); var=cats(put(cnt,best.),var); run;
Please post example data in a data step (see my footnotes), and the expected output from that.
data test; input NEW_PROVIDER_NUMBER $ NEW_PROVIDER_NUMBER_SUFFIX $ NEW_LOC_SEQ MAX_NEW_SEQ; cards; 003219469 00 4 4 003219469 00 3 14 003219469 00 2 4 003220507 00 98 105 003220507 00 99 105 003262020 00 100 100 003220507 00 98 105 003220507 00 99 105 ; run; LOGIC: IF MAX_NEW_Sequence_Number >= 100, then NEW_Sequence_Number between 0 and 99, populate '01' in NEW_Provider_Number_Suffix IF NEW_Sequence_Number >=100 then, populate '02' in NEW_Provider_Number_Suffix and add 1 to NEW_Sequence_Number.
Thats great, but
NEW_Sequence_Number
Is never defined? What is new sequence number? Is it just observation number?
As such I still go with:
data test; input new_provider_number $ new_provider_number_suffix $ new_loc_seq max_new_seq; cards; 003219469 00 4 4 003219469 00 3 14 003219469 00 2 4 003220507 00 98 105 003220507 00 99 105 003262020 00 100 100 003220507 00 98 105 003220507 00 99 105 ; run; data want (drop=cnt); set test; retain cnt 1; if _n_/100 =0 then cnt=sum(cnt,1); new_provider_number_suffix=strip(put(cnt,z2.)); run;
That is new_loc_seq
Then I really do not know what the problem is, or why your question refers to "multiple conditons across observations", as from what you have said and provided:
data test; input new_provider_number $ new_provider_number_suffix $ new_loc_seq max_new_seq; cards; 003219469 00 4 4 003219469 00 3 14 003219469 00 2 4 003220507 00 98 105 003220507 00 99 105 003262020 00 100 100 003220507 00 98 105 003220507 00 99 105 ; run; data want; set test; if 0 <= new_loc_seq <= 99 then new_provider_number_suffix="01"; else new_provider_number_suffix="02"; run;
This is my requirements.
If MAX NEW_Sequence_Number >= 100,
For records with NEW_Sequence_Number between 0 and 99, populate '01' in
For records with NEW_Sequence_Number >=100, populate '02' in NEW_Provider_Number_Suffix and add 1 to NEW_Sequence_Number.
data test2; set test1; if MAX_NEW_SEQ >=100 then do; if NEW_LOC_SEQ >=0 and NEW_LOC_SEQ =< 99 then NEW_PROVIDER_NUMBER_SUFFIX='01'; end; else if NEW_LOC_SEQ >=100 then do; NEW_PROVIDER_NUMBER_SUFFIX='02'; NEW_LOC_SEQ_N=input(NEW_LOC_SEQ,8.) +1; end; run;
So something like:
data test; input new_provider_number $ new_provider_number_suffix $ new_loc_seq max_new_seq; cards; 003219469 00 4 4 003219469 00 3 14 003219469 00 2 4 003220507 00 98 105 003220507 00 99 105 003262020 00 100 100 003220507 00 98 105 003220507 00 99 105 ; run; data want; set test; retain new_sequence_number 0; if 0 <= new_loc_seq <= 99 then new_provider_number_suffix="01"; else do; new_provider_number_suffix="02"; new_sequence_number=sum(new_sequence_number,1); end; run;
It seems that you miss ELSE after first END;
data test2;
set test1;
if MAX_NEW_SEQ >=100 then do;
if NEW_LOC_SEQ >=0 and NEW_LOC_SEQ =< 99 then
NEW_PROVIDER_NUMBER_SUFFIX="01";
end; else
if NEW_LOC_SEQ >=100 then do;
NEW_PROVIDER_NUMBER_SUFFIX='02';
NEW_LOC_SEQ_N=input(NEW_LOC_SEQ,8.) +1;
end;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.