Parsing in SAS 1

[sap1958]

This is the txt file for input
VA³00002³0351³02³20041³DRUG
Here is my code
data VAUTIL03;
infile 'c:\VAUTIL03.txt';
INPUT STATE $ NDC1 NDC2 NDC3 YRQTR DRUG $ ;
run;

I want to get rid of the small 3 that serves as the delimiter or separator. The only text fields are state and drug. How would I do this as a parse

 

[MatthiasB]

Just specify the funny 3 as a delimiter on the infile and you're set. I also suggest to specify the length of all variables on introduction - it's good practice and avoids any issue with cut-off strings or similar:

data VAUTIL03;
length STATE $2
NDC1 8
NDC2 8
NDC3 8
YRQTR 8
DRUG $10
;
infile 'c:\VAUTIL03.txt' DLM='³';
INPUT STATE NDC1 NDC2 NDC3 YRQTR DRUG;
run;

 

[sap1958]

MatthiasB,
thanks, your solution worked perfectly. One more question. Lets say I want to read in multiple txt files with the same scenario. Here is how I handled multiples
/*first one*/
data VAUTIL03;
length STATE $2
NDC1 8
NDC2 8
NDC3 8
YRQTR 8
DRUG $10
Units 8 /*3 decimal places*/
Scripts 8
MedicaidAmt 8 /*2 decimal place*/
Other1 8 /*2 decimal place*/
Other2 8; /*2 decimal place*/

infile 'c:\VAUTIL03.txt' DLM='³';
INPUT STATE NDC1 NDC2 NDC3 YRQTR DRUG Units Scripts MedicaidAmt Other1 Other2 ;
run;

/*second one*/
data VAUTIL04;
length STATE $2
NDC1 8
NDC2 8
NDC3 8
YRQTR 8
DRUG $10
Units 8 /*3 decimal places*/
Scripts 8
MedicaidAmt 8 /*2 decimal place*/
Other1 8 /*2 decimal place*/
Other2 8; /*2 decimal place*/


infile 'c:\VAUTIL04.txt' DLM='³';
INPUT STATE NDC1 NDC2 NDC3 YRQTR DRUG Units Scripts MedicaidAmt Other1 Other2 ;
run;

Is there a way I could have referenced combined the two txt files in one data step statement or is this the best way to handle multiple txt files. Just asking cause I may have about 300 of these

 

[MatthiasB]

Hi SAP1958,

should go in under 60 lines of code, get introduced to SAS/Macro:

%* MACRO - read all files w/pattern FP in folder BDIR  ;
%*         store the vautil files in table DS          ;
%macro rdVAUTIL (bdir=, fp=, ds=);

  %local numFiles;
  %let numFiles=0;

  %*--  find all files to read in.  ;
  filename dosdir pipe "dir /b ""&BDIR.\&FP.""";
  data _null_;
    length aFile $100;
    infile dosdir;
    input aFile;
    call symput ('myFile' !! left (put (_n_, BEST.)), aFile);
    call symput ('numFiles', put (_n_, BEST.));
  run;

  %*--  loop over all files, read into single table.  ;
  %do idx=1 %to &NUMFILES.;

    %*--  read the next table.  ;
    data WORK.__tmpV;
      length STATE       $2
             NDC1        8
             NDC2        8
             NDC3        8
             YRQTR       8
             DRUG        $10
             Units       8    %* 3 decimal places ;
             Scripts     8
             MedicaidAmt 8    %* 2 decimal places ;
             Other1      8    %* 2 decimal places ;
             Other2      8    %* 2 decimal places ;
             srcFile     $100
             ;
      infile "&BDIR.\&&MYFILE&idx.." DLM='³';
      INPUT STATE NDC1 NDC2 NDC3 YRQTR DRUG
            Units Scripts MedicaidAmt Other1 Other2
            ;
      %*--  remember where we got which data line from.  ;
      srcFile = "&&MYFILE&idx..";
    run;

    proc append base=WORK.AllVAUTIL
                data=WORK.__tmpV;

  %end;

  %*--  cleanup.  ;
  proc datasets lib=WORK nolist;
                delete __tmpV;
  quit;

%mend;

%*--  execute the macro, all files in ALLVAUTIL table.  ;
%rdVAUTIL (  bdir = C:\VautilDir
           , fp   = *.txt
           , ds   = WORK.allVautil);

Macro is a pre-processor, it is not a "SAS library" where you can write functions and call them - SAS macros and macro variables work differently. If you're new to that subject, see support.sas.com, they should have the macro manual as PDF download. Macro basically "builds" SAS code which is then executed, so macro is ideal for repetitive similar work and for things to be parametrized.

The code assumes you place all the files to be read in into a single folder (here: C:\vautilDir), they all have extension "txt", have the same column layout. All filenames are read into macro variables, for each a tmp table holds the content which is appended to the final table referenced by the DS parameter - enjoy the lengthy log with >300 files to read