topic Re: %DO %UNTIL loop execution help in SAS Programming

%DO %UNTIL loop execution help

drcline87 — Wed, 01 Mar 2017 15:47:50 GMT

I have extensive experiance with VBA so I know that this can be done but I do not know the proper syntax for it as I have limited experiance with SAS coding. I have code that extracts data from pre set text files and aranges them into 74 Excel files. Currently the code is run one at a time, changing the x value at each iteration. I would like the code to run as a DO UNTIL loop. Any idea how to do this? Here is the code:

 %MACRO TEST;
%global inputfilename outputfilename x j;
%put &x;
%put &inputfilename;
%put &outputfilename;
%let x = 1;
%If &x = 1 %then %do;
%let inputfilename = C:\readtest4\P3674100.txt;
%let outputfilename = C:\Temp4\001 [NAME REDACTED] 4.xlsx;
%end;
%If &x = 2 %then %do;
%let inputfilename = C:\readtest4\P4135008.txt;
%let outputfilename = C:\Temp4\002 [NAME REDACTED] 4.xlsx;
%end;
*(CODE CONTUNUES IN LIKE MANNER)*
%If &x = 73 %then %do;
%let inputfilename = C:\readtest4\P4138601.txt;
%let outputfilename = C:\Temp4\050 [NAME REDACTED] 4.xlsx;
%end;
%If &x = 74 %then %do;
%let inputfilename = C:\readtest4\P4135300.txt;
%let outputfilename = C:\Temp4\006 [NAME REDACTED] 4.xlsx;
%end;

%MEND;
DATA ONE;
%TEST
INFILE "&inputfilename" LRECL=135;
INPUT @1 PROVNO $7.
@8 LNAME $12. 
@20 FNAME $7.
@27 MINIT $1.
@28 ACCTNO $20.
@48 ICN $11.
@59 J3DATE $6.
@65 CLCHARGE 10.2
@75 DOCCHARGE 10.2
@85 TPLPAID 10.2
@95 TPLDOC 10.2
@105 IDNO $1.
@106 CTYPE $2.
@108 SERDAT $6.
@108 SYR 2.
@110 SYMON 2.
@112 SYDAY 2.
@114 TRANSTYPE $1. 
@115 PAYAMT 10.2 
@125 BirDate $8.
@125 DoBYR 4.
@129 DoBMO 2.
@131 DoBDA 2.
@133 PATAGE 3. ;
YEARCUTOFF = 1904;
RUN;
DATA TWO;
SET ONE;
IF PATAGE > 109 THEN DO; 
AgeDays = Serdat - BirDate;
PatAge = Int(AgeDays/365.25);
END;
DROP SYr SyMon SyDay DObYR DoBMo DoBDa AGEDAYS YEARCUTOFF;
RUN;
DATA EDIT; /** THIS DATA SET IS ONLY FOR REVIEW PURPOSES- RECORDS ARE NOT ELIMINATED ***/
SET ONE;
IF SYR GT '16';
RUN;
PROC PRINT;
SUM CLCHARGE DOCCHARGE PAYAMT;
TITLE 'EDIT REPORT -- SERVICE DATE YEAR GREATER THAN AUDIT YEAR'; 
RUN;
PROC EXPORT 
%TEST
DATA=WORK.TWO
OUTFILE="&outputfilename"
DBMS=XLSX REPLACE;
RUN;

Re: %DO %UNTIL loop execution help

RW9 — Wed, 01 Mar 2017 16:08:29 GMT

Sorry, I can't even look at that code.

What I would do is start again. Look at the process logically, You need to read a file, then group that out to multiple output files (I wont ask why). So step 1 load your text file:

data one;
  infile "<pathtofile>\<file>.txt" lrecl=135;
  input @1 provno $7.
        @8 lname $12. 
        @20 fname $7.
        @27 minit $1.
        @28 acctno $20.
        ...;
run;

Step 2, assign a variable in that data to indicate which file the data element should go out to:

data one;
  set one;
  output_file=cats("file",put(floor(_n_/100)+1,z8.));
run;

I have just assigned each 100 rows to a new filename.

Step 3, finally we use a distinct dataset to loop and generate the code export the data:

proc sort data=one out=loop nodupkey;
  by output_file;
run;

data _null_;
  set loop;
  call execute(cats('proc export data=one (where=(output_file="',output_name,'")) 
                     outfile="<path>\',output_name,'.xlsx"; run;'));
run;

Note, minor update to use cats() function rather than || and strip.

Re: %DO %UNTIL loop execution help

jklaverstijn — Wed, 01 Mar 2017 16:10:37 GMT

Oh where to begin. Your code has some issues that indicate a misunderstanding of some basic macro principles. To start, %TEST does not generate any SAS code so adding this to a datastep does exactly nothing. I suggest you read ujp on macro's first.

Luckiliy you may not need them at this point. If you look at the filevar option to the INFILE and FILE statements you may be able to use this:

DATA ONE;
   INFILE CARDS;
   LENGTH FILENAME $40;
   INPUT FILENAME $;
   INFILE "&inputfilename" filevar=filename LRECL=135 END=DONE;
   DO WHILE (NOT ONE);
      INPUT @1 PROVNO $7.
      @8 LNAME $12. 
      ... etc ...
      @133 PATAGE 3. ;
      YEARCUTOFF = 1904;
      OUTPUT;
   END;
CARDS;
c:\readtest4\P4135601.txt
c:\readtest4\P4135300.txt
... etc ...
RUN;

This makes your input files datadriven. Nice and tight code. No macro's.

Also consider eliminating datasets TWO and EDIT by moving that code in a single datastep.

Hope this helps,

- Jan.

Re: %DO %UNTIL loop execution help

JerryV — Wed, 01 Mar 2017 16:15:59 GMT

Perhaps something like this? If you have blanks in inputfilename or outputfilename adjustment will be necessary.

/* assuming your inputfilename and outputfilnames do not contain blanks */

%MACRO TEST(inputfilename, outputfilename);

DATA ONE;

INFILE "&inputfilename" LRECL=135;

INPUT @1 PROVNO $7.

@8 LNAME $12.

@20 FNAME $7.

@27 MINIT $1.

@28 ACCTNO $20.

@48 ICN $11.

@59 J3DATE $6.

@65 CLCHARGE 10.2

@75 DOCCHARGE 10.2

@85 TPLPAID 10.2

@95 TPLDOC 10.2

@105 IDNO $1.

@106 CTYPE $2.

@108 SERDAT $6.

@108 SYR 2.

@110 SYMON 2.

@112 SYDAY 2.

@114 TRANSTYPE $1.

@115 PAYAMT 10.2

@125 BirDate $8.

@125 DoBYR 4.

@129 DoBMO 2.

@131 DoBDA 2.

@133 PATAGE 3. ;

YEARCUTOFF = 1904;

RUN;

DATA TWO;

SET ONE;

IF PATAGE > 109 THEN DO;

AgeDays = Serdat - BirDate;

PatAge = Int(AgeDays/365.25);

END;

DROP SYr SyMon SyDay DObYR DoBMo DoBDa AGEDAYS YEARCUTOFF;

RUN;

DATA EDIT; /** THIS DATA SET IS ONLY FOR REVIEW PURPOSES- RECORDS ARE NOT ELIMINATED ***/

SET ONE;

IF SYR GT '16';

RUN;

PROC PRINT;

SUM CLCHARGE DOCCHARGE PAYAMT;

TITLE 'EDIT REPORT -- SERVICE DATE YEAR GREATER THAN AUDIT YEAR';

RUN;

PROC EXPORT

DATA=WORK.TWO

OUTFILE="&outputfilename"

DBMS=XLSX REPLACE;

RUN;

%MEND TEST;

Data _null_;

length inputfilename outputfilename $128 ;

infile datalines truncover pad ;

input @01 inputfilename :$128.

@27 outputfilename $128. ;

call execute('%TEST('||inputfilename||','||outputfilename||');');

datalines;

C:\readtest4\P3674100.txt C:\Temp4\001 [NAME REDACTED] 4.xlsx

C:\readtest4\P4135008.txt C:\Temp4\002 [NAME REDACTED] 4.xlsx

... ...

C:\readtest4\P4138601.txt C:\Temp4\050 [NAME REDACTED] 4.xlsx

C:\readtest4\P4135300.txt C:\Temp4\006 [NAME REDACTED] 4.xlsx

;;;;

Re: %DO %UNTIL loop execution help

Reeza — Wed, 01 Mar 2017 17:26:48 GMT

Ouch, that's a tedious program. Refactoring is a good idea. If I understand correctly, you're importing X amount of files, processing them identically (?) and then exporting to Excel.

1. Are all the text files and Excel files the same? If so, read all the text files at once and process at once. How do you know which Excel files goes with which number? Is there a rule and most importantly, does it even matter 🙂

2. The PROC EXPORT appears correct. Using CALL EXECUTE to export the data is better.

For Step 1, see this post:

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-text-files-that-have/ta-p/223627

For Step 2, see Call Execute documentation or another users post. You can use your dataset from above to drive it and create the names required dynamically.