if with multiple conditions across observations

[Spintu]

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;

[PeterClemmensen]

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.

[Spintu]

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         

[RW9]

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:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

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;

[Kurt_Bremser]

Please post example data in a data step (see my footnotes), and the expected output from that.

[Spintu]

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.

[RW9]

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;

[Spintu]

That is new_loc_seq 

[RW9]

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;

[Spintu]

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;

[RW9 · Accepted solution]

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;

[Spintu]

Thank you

[Spintu]

thank you for sugesstion

[Shmuel]

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;

[Spintu]

Thank you Sir for your quick sugesstion