Pick values fall under thru statement

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Pick values fall under thru statement

Hi,

I am doing a conversion program from DYL280 language to SAS.

In DYL280, there was one logic as follows:

Read readmast:

If leadmast = '01' THRU wperand then next else reject

If locdata > '00000' then readmast else reject

If locdata >= '50000' then readmast else reject

****************************************************************

Binsearch file2 file3

If found Y, then

List Area = 'Southeren bay'

Else goto readmast.

How to code this logic in SAS? How to code THRU logic in SAS and also binary search?

Note: wperand is a 2 digit numeric field. May have any value from 00 to 99.

If it hit reject in DYL280 language, then program needs to read the next record and skip this current record.

Please help me to write a logic for this.

Thank you.

Santhosh


Accepted Solutions
Solution
‎08-13-2015 02:18 PM
Super User
Posts: 10,500

Re: Pick values fall under thru statement

Yep, that's and update. Here is one form of update using Proc Sql. I built the data sets using fixed columns as it looked like your original locations MAY have used tabs and didn't quite align with the data as posted.


data file1;
input
@01 Name               $12.
@14 zipcode             05.
@20 Area1              $03.
@23 dept                04.
@28 ldper               02.;
datalines;
JohnAbraham  80000 AY 1900 09
Madisson     75200 AY 1825 01
Murphy       95210 AY 1952 06
Donaldmathew 65214 NY 6854 18
Eric         85102 NA 7889 24
;
run;


data File2;
input
@01 Tloc            05.
@07 tarea1         $03.
@10 tdept1          04.;
datalines;
55589 LA 1980 10
75200 LA 1825 01
87510 HT 1952 06
65214 LO 6854 18
74502 NA 7889 24
;
run;

If you have further questions on different topics, please create new threads in the forum. That way you can mark one as answered as you are satisfied with the responses and folks know that you don't need further help on that issue.

Limited single topics are preferred as trying to keep different bits going with replies not always near the topic replied too gets confusing and error prone.
proc sql;
   update file1 as A
   set Area1 = (select tarea1 from file2 as B
                       where b.Tloc = A.Zipcode)
  ;
quit;

Obnoxious part of SAS proc SQL, you can only update one variable at a time. There is also a data step merge with different options.

View solution in original post


All Replies
Super User
Posts: 10,500

Re: Pick values fall under thru statement

Note SAS only uses quotes for character variables so use of '01' may not be appropriate. Though in many cases if you mix character and numeric variables then SAS will note in the log that some conversion may have been done

To test if a value is within a range of numbers one way is:

If 01 <= leadmast <= wperand then <do what ever> .

if you don't want the equality one either end then drop the "=" part for the comparison.

In a basic SAS datastep program;

Data want;

     set have; <this would be the source data set if the data is already in SAS, OR and INPUT statement if reading an external file>

     If 01 <= leadmast <= wperand;  < out put would only have records where this is true statement, an implied ELSE DELETE in effect>

    (or this statement)

      if not ( 01 <= leadmast <= wperand) ; <output would only have records where this statement is true, depending  on how your reject actually works, this may be what you want in this case or alternately

     if leadmast < 01 or leadmast > wperand; these last 2 IFs are logically the same>

run;

In SAS logic this bit:

If locdata > '00000' then readmast else reject

If locdata >= '50000' then readmast else reject

If the first is true then the second is always true, so I'm not quite sure what may be going on here.

Your question on BINSEARCH would need more details as to what DYL280 does with that. If you are looking through all of the rows of multiple datasets then we'll likely get into the world of SAS macro programing.

Asking questions on this forum it really helps to provide a few example records and what the desired output should look like, and if it is a report or dataset desired. That provides a more concrete example of the rules discussed and helps clarify with fewer words than English may require.

Contributor
Posts: 20

Re: Pick values fall under thru statement

Thank you so much. Your answer was really helpful. Thanks again. I am working in a healthcare project and so I can't post the data here, But I will create one dummy dataset soon and I will post it.

01 <= leadmast <= wperand;worked well.

In binary search DYL280 code was as follows:

***************************************************************************

BINSEARCH File1 file2;

If found Y then Move Tarea(File1) to Area1(File2). Endif Goto next record.

Else LIST "Job code not found" LDCC

Goto next record.
****************************************************************************

LIST "Job code not found" LDCC

This list should be routed to SYS280R DD SYSOUT=* in job log and not to be written in  any output files. Please help me to code this.As i told, I will post dummy input files soon.

Super User
Posts: 10,500

Re: Pick values fall under thru statement

PUT statements inside a SAS Data step  (any code section starting with DATA ) OR %put will by default send output to the log output. If your SYS280R DD SYSOUT=* is not the default log then there would be other things to consider.

For any help with this:

BINSEARCH File1 file2;

If found Y then Move Tarea(File1) to Area1(File2). Endif Goto next record.

Else LIST "Job code not found" LDCC

Goto next record.

You'll need to explain what it does. It seems to me like it may be more of an operating system instruction script that is running here and that may not be optimal to attempt in SAS.  Are File1 and File2 SAS data sets (or to become SAS data sets in the migration)? Does MOVE here mean to move a file or data set to a different storage location?

Contributor
Posts: 20

Re: Pick values fall under thru statement

Thanks much for your response. Binary search is kind of lookup between two input files(datasets) and if KEY filed was found then move Tarea field value in file1 to Area1 field in file2. Else need to perform LIST command to write the value in sas log.

Super User
Posts: 10,500

Re: Pick values fall under thru statement

This sounds more like some form of update. If you could provide some example data, dummy is fine, of what file1 and file2 might look like on input and what the final result should be we might be able to help.

Contributor
Posts: 20

Re: Pick values fall under thru statement

Hi,

I am mentioning a sample layout of input and output files. Thank you.

File1: Profile

@01 Name               $12.

@11 zipcode             05.

@15 Area1              $03.

@18 dept                04.

@22 ldper               02.;

Profile File layout:

JohnAbraham  80000 AY 1900 09

Madisson        75200 AY 1825 01

Murphy           95210 AY 1952 06

Donaldmathew65214 NY 6854 18

Eric                85102 NA 7889 24

File2: Location

@01 Tloc            05.

@06 tarea1         $03.

@09 tdept1          04.;

Location file layout:

55589 LA 1980 10

75200 LA 1825 01

87510 HT 1952 06

65214 LO 6854 18

74502 NA 7889 24

Requirement:

Need to do a lookup/search between Location and profile files(It will be good, if we can update this profile file without sorting the key field).

Key fields are Zipcode in Profile file and Tloc field in Location file. If matches then

Move tarea1 to Area1. else

move spaces to Area1 field. Update these fields in the profile file based upon the lookup logic.

Output file will be as follows:

File Final1:

Layout:

JohnAbraham  80000      1900 09

Madisson        75200 LA 1825 01

Murphy           95210      1952 06

Donaldmathew65214 LO 6854 18

Eric                85102      7889 24

Note: File2: (Location) should not be captured in the final output file.

This file is used only to update the value in tarea1 field based on lookup condition if key matches.

Solution
‎08-13-2015 02:18 PM
Super User
Posts: 10,500

Re: Pick values fall under thru statement

Yep, that's and update. Here is one form of update using Proc Sql. I built the data sets using fixed columns as it looked like your original locations MAY have used tabs and didn't quite align with the data as posted.


data file1;
input
@01 Name               $12.
@14 zipcode             05.
@20 Area1              $03.
@23 dept                04.
@28 ldper               02.;
datalines;
JohnAbraham  80000 AY 1900 09
Madisson     75200 AY 1825 01
Murphy       95210 AY 1952 06
Donaldmathew 65214 NY 6854 18
Eric         85102 NA 7889 24
;
run;


data File2;
input
@01 Tloc            05.
@07 tarea1         $03.
@10 tdept1          04.;
datalines;
55589 LA 1980 10
75200 LA 1825 01
87510 HT 1952 06
65214 LO 6854 18
74502 NA 7889 24
;
run;

If you have further questions on different topics, please create new threads in the forum. That way you can mark one as answered as you are satisfied with the responses and folks know that you don't need further help on that issue.

Limited single topics are preferred as trying to keep different bits going with replies not always near the topic replied too gets confusing and error prone.
proc sql;
   update file1 as A
   set Area1 = (select tarea1 from file2 as B
                       where b.Tloc = A.Zipcode)
  ;
quit;

Obnoxious part of SAS proc SQL, you can only update one variable at a time. There is also a data step merge with different options.

Contributor
Posts: 20

Re: Pick values fall under thru statement

Hi,

Thank you so much for sending the PROC SQL query. Its working fine but I was unable to update the same for more than one field. Will post this issue in a new topic. Thanks again.

Super User
Posts: 10,500

Re: Pick values fall under thru statement

Note the text after the code:

Obnoxious part of SAS proc SQL, you can only update one variable at a time.

Contributor
Posts: 20

Re: Pick values fall under thru statement

So If I want to do it for second update, Am I need to write one step extra for this? And I am getting spaces for unmatched records between two files. I dont want unmatched records in updated file. I raised the same issue in another topic."Unable to update more than one fields using PROC SQL". Could you please guide me. It will be a great help. Thank you.

Super User
Posts: 10,500

Re: Pick values fall under thru statement

I think you need to provide a very complete clearly specified input and desired output. The suggestion I made created an example set as you showed.

Likely you actually want a data step update.

Contributor
Posts: 20

Re: Pick values fall under thru statement

Requirement is:

First lookup to select NAME and loc:

Need to do a lookup between file1 and file2, whereas name was the key field. IF found between two files then update file1 by moving TLOC(in file2) TO ZIPCODE(in file1).

If not found then list 'NAME NOT FOUND' NAME in sas log(Note: value in the name field for that unmatched record should be written in the saslog as well) and reject that record .

And drop all unmatched records and update file1 with only matched records.

DYL280 code:

****SELECT NAME****

BINSEARCH FILE2 NAME

IFFOUND EQ 'Y' NEXT

ELSE LIST 'NAME NOT FOUND' NAME

GOTO NEXTRECORD ENDIF

MOVE TLOC TO ZIPCODE

Second lookup between file1 and file3, where as DEPT was the key field. IF found between two files AND TAREA1 NOT EQUAL TO 'AYA' then update file1 by moving TAREA2(in file2) TO TAREA(in file1) and AREADESC(in file2) TO LDESC(in file1).

If not found and TAREA1 EQ 'AYA'  then list 'DEPT NOT FOUND IN FILE3' DEPT in sas log.And drop all unmatched records and update file1 with only matched records.

****REJECT ANY DEPT CCS, GET CC DECS****

BINSEARCH FILE3 DEPT

IFFOUND EQ 'Y' NEXT

ELSE LIST 'DEPT NOT FOUND IN FILE3' DEPT

GOTO NEXTRECORD ENDIF

IF TAREA1 EQ 'AYA' GOTO READMAST ENDIF

MOVE TAREA2 TO TAREA

MOVE AREADESC TO LDESC

SAS code we tried for first logic: But unable to drop unmatched records and update more than one variable at a time. And unable to list down the nonmatched field value in SAS log.

proc sql;

   update file1 as A

   set zipcode = (select tloc from file2 as B

                       where b.Tname = A.Name)

  ;

quit;

Input file layout and sample records:

data file1;

input

@01 Name               $12.

@14 zipcode             05.

@20 Area1              $03.

@23 dept                04.

@28 ldper               02.

@30 ldesc               30.;

datalines;

JohnAbraham  80000 AY 1900 09

Madisson     75200 AY 1825 01

Murphy       95210 AY 1952 06

Donaldmathew 65214 NY 6854 18

Eric         85102 NA 7889 24

;

run;

data File2;

input

@01 TName           $12.

@13 Tloc            05.

@18 tarea1         $03.

@21 tdept1          04.;

datalines;

Mathews55589 LA 1980 10

Jonny  75200 LA 1825 01

David  87510 HT 1952 06

Edrick 65214 LO 6854 18

Monaay 74502 NA 7889 24

;

run;

data File3;

input

@01 Tloc1            05.

@07 tarea2         $03.

@10 tdept2          04.

@14 areadesc       30.;

datalines;

55589 LA 1980 10 SOUTHEREN CALIFORNIA

75200 LA 1825 01

87510 HT 1952 06 EASTERAN BAY

65214 LO 6854 18 NEWYORK

74502 NA 7889 24 WASHINGTON.;

RUN;

Contributor
Posts: 20

Re: Pick values fall under thru statement

I am trying to mask my data and load it here. Mostly by this time tomorrow, I will upload it.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 452 views
  • 0 likes
  • 2 in conversation