topic Do while and do until alternatives to loop through a dataset in SAS Programming

Do while and do until alternatives to loop through a dataset

Jumboshrimps — Mon, 21 Oct 2019 17:17:53 GMT

Neither the do While or Do Until loop are working as to be expected.

First example is Do While, there are four records in data set "ds" that meet the criteria,

The dsname variable of the first record is passed to the macro, which unzips the data set with

an extension of ".gz", Otherwise the dataset is not touched, Loop terminates after one record in unpacked.

For this dataset - which is just a list of file names in the folder -

/data/dev/share/test - four records meet the criteria.

The variable "ddname" correctly resolves to dsname, which is simply the name of the .gz dataset.

Code to create data set of file names in directory:

%let path='/data/dev/share/test';

filename parent "&path";

data files;
length fullfilename $90;
length dsname $34;
length ext $3;
/*declaring ext(tension) var, but not populating, same with dsname */
drop rc did i;
did=dopen("parent");
if did > 0 then do;
do i=1 to dnum(did);
fullfilename=dread(did,i);
output;
end;
rc=dclose(did);
end;
else put 'Could not open directory';
run;

data ds;
set files;
/*now populate ext var, dsname */
ext=substr(fullfilename,length(fullfilename)-2,3);
dsname=substr(fullfilename,1,index(fullfilename, '.')-1);
run;

Do while Loop (doesn't work):

DATA _null_;
length ddname $67.;
DATA _null_;
set ds;
do while (ext = ".gz");
call symput('ddName', dsName);
%Macro1(data/dev/share/test,&ddname /data//dev/share/tmp);
END;
RUN;

The Do Until does no better:

DATA _null_;
length ddname $67.;
DO UNTIL (EOF);
SET ds END=EOF;
WHERE ext = ".gz";
call symput('ddName', dsName);

%Macro1(/data/dev/share/test,&ddname,/data/dev/share/tmp);
END;
RUN;

Again, the variable "ddName" correctly resolves to the name of the first record of a .gz

data set, no more records are processed after the first data set is unpacked.

I need the loops to continue until all the records are processed that meet the

criteria ext = "gz", not just one. There may be several hundred data sets in the folder,

a couple hundred sas7bdat, a couple hundred sas7bdat.gz

What alternatives are there for looping through a data set and then acting (passing variable dsname to

a macro that unpacks the compressed datasets to a temporary folder)?:

Re: Do while and do until alternatives to loop through a dataset

Tom — Mon, 21 Oct 2019 17:37:09 GMT

Why do you have a call to a macro in the middle of a data step? Does the macro only generate part of a data step?

Can you show the macro you are trying to call?

Re: Do while and do until alternatives to loop through a dataset

ballardw — Mon, 21 Oct 2019 18:06:35 GMT

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

I am going to guess that you expect the macro to be executed once for each record. It sounds like you expect that macro to read external data either with Proc Import or something else. The data step calling the Macro would stop in that case as you would have one of the "boundaries" such as a Proc or another Data statement that would end the first data step.

Something like this may get closer:

DATA _null_; 
   set ds; 
   where ext = ".gz";
   length str $ 100;
   str = (cats,'%Macro1(data/dev/share/test,', dsname,'/data//dev/share/tmp)');
   call execute (str);
RUN;

One reason I say "may" is that there are two quite different calls to your macro .

With the above you might consider actually creating a data set and see if the values of the variable STR look like the macro calls you want to make if this doesn't quite work.

This is one place where you do not want to use double quotes in the function around %macro1. Use of single quotes should prevent it from attempting to execute during the data step. Call Execute stacks code to be executed after the data step completes.

Or write the STR values out to a text file and use %include to execute the file for execution.

Re: Do while and do until alternatives to loop through a dataset

Tom — Mon, 21 Oct 2019 18:16:53 GMT

What are you having trouble with exactly? Is the first data step that appears to find file names work?

If so then show an example of two or three of the values that it should generate.

Are you just trying to use the data from that dataset to generate calls to your macro?

Assuming you macro takes two inputs. First the file name of the .gz file and second the member name of the dataset you want it to create then your program might look like this. No DO loops needed.

data _null_;
  set files;
  length ext $20 dsname $32 ;
  if index(fullfilename,'.') then ext=scan(fullfilename,-1,'.');
  if ext='gz';
  dsname = scan(fullfilename,-2,'./\');
  call execute(cats('%nrstr(%macro1)(',fullfilename,',',dsname,')'));
run;

Re: Do while and do until alternatives to loop through a dataset

Jumboshrimps — Mon, 21 Oct 2019 19:41:40 GMT

I had to clean up the log extensively (below).
Macro is working, resolving ddname as the name of the dataset to be gunzipped, folders are hard coded an correctly resolved as ZIPFLPTH and UNZPFLPTH respectively. All loops I tried just stop at one record
Also using DO I=1 TO N; returns the same result: Bottom record is processed to the macro, all other records are not processed.

1191 %c14_uzip_v3(/data/dev/share/test,&ddname,data/dev/share/tmp);
MLOGIC(C14_UZIP_V3): Beginning execution.
SYMBOLGEN: Macro variable DDNAME resolves to cmu
MLOGIC(C14_UZIP_V3): Parameter ZIPFLPTH has value /data/dev/share/test
MLOGIC(C14_UZIP_V3): Parameter ZPFLNAME has value cmu
MLOGIC(C14_UZIP_V3): Parameter UNZPFLPTH has value data/dev/share/tmp
MLOGIC(C14_UZIP_V3): %LET (variable name is FILREF)
SYMBOLGEN: Macro variable ZIPFLPTH resolves to /data/dev/share/test
SYMBOLGEN: Macro variable FILREF resolves to /data/dev/share/test
SYMBOLGEN: Macro variable ZPFLNAME resolves to cmu
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable FILREF resolves to /data/dev/share/test
SYMBOLGEN: Macro variable ZPFLNAME resolves to cmu
MLOGIC(C14_UZIP_V3): %IF condition (%sysfunc(exist(&filref..&zpflname.)) = 0) &&
%sysfunc(fileexist("&filref./&zpflname..sas7bdat.gz")) is TRUE
MPRINT(C14_UZIP_V3): x filedef xdf clear;
SYMBOLGEN: Macro variable ZIPFLPTH resolves to /data/dev/share/test
SYMBOLGEN: Macro variable ZPFLNAME resolves to cmu
SYMBOLGEN: Macro variable UNZPFLPTH resolves to data/dev/share/tmp
SYMBOLGEN: Macro variable ZPFLNAME resolves to cmu
MPRINT(C14_UZIP_V3): x "gunzip -c /data/dev/share/test/cmu.sas7bdat.gz >
data/dev/share/tmp/cmu.sas7bdat";
SYMBOLGEN: Macro variable UNZPFLPTH resolves to data/dev/share/tmp
SYMBOLGEN: Macro variable ZPFLNAME resolves to cmu
MPRINT(C14_UZIP_V3): x "chmod 770 data/dev/share/tmp/cmu.sas7bdat";
SYMBOLGEN: Macro variable UNZPFLPTH resolves to data/dev/share/tmp
SYMBOLGEN: Macro variable ZPFLNAME resolves to cmu
MPRINT(C14_UZIP_V3): x "chgrp group1 data/dev/share/tmp/cmu.sas7bdat";
MLOGIC(C14_UZIP_V3): Ending execution.
1192 END;
1193 RUN;

Re: Do while and do until alternatives to loop through a dataset

Tom — Mon, 21 Oct 2019 20:01:43 GMT

Those SYMBOLGEN and MLOGIC lines are probably just making your log confusing.

So it looks like your macro generates some X commands.

1191 %c14_uzip_v3(/data/dev/share/test,&ddname,data/dev/share/tmp);
MPRINT(C14_UZIP_V3): x filedef xdf clear;
MPRINT(C14_UZIP_V3): x "gunzip -c /data/dev/share/test/cmu.sas7bdat.gz > data/dev/share/tmp/cmu.sas7bdat";
MPRINT(C14_UZIP_V3): x "chgrp group1 data/dev/share/tmp/cmu.sas7bdat";

Why are you trying to run those in the middle of a data step? That doesn't make any sense. You are essentially doing this:

data xxx;
  do i=1 to 5;
   x filedef xdf clear;
   x "gunzip -c /data/dev/share/test/cmu.sas7bdat.gz > data/dev/share/tmp/cmu.sas7bdat";
   x "chgrp group1 data/dev/share/tmp/cmu.sas7bdat";
  end;
run;

Which will just run the same three X commands 5 times.

NOTE the missing root node on the output directory name is also probably not helping. data/... is a relative path, while /data/... is an absolute path.

Instead you probably want to take the value of some data step variable and use it to replace the reference to the macro variable DDNAME in the macro call and generate the macro calls to run after the data step finishes.

So if your input dataset is named HAVE and it has a variable named NAME with the value that corresponds to the value of &DDNAME then the code might look like this:

data xx;
  set have;
  call execute(cats
('%nrstr(%c14_uzip_v3)(/data/dev/share/test'
,',',NAME
,',','/data/dev/share/tmp)'
));
run;

Although not sure you need the macro at all. Whatever logic you had in the macro (calls to the EXIST() and FILEEXIST() functions) could probably be done easier in a data step instead. You could then just generate the X commands directly using the data step.

Re: Do while and do until alternatives to loop through a dataset

Tom — Mon, 21 Oct 2019 20:10:45 GMT

If you can run X commands then why are you making getting the list of filenames so hard?

%let path=/data/dev/share/test ;

data files ;
  infile "ls &path/*.sas7bdat.gz" pipe truncover ;
  input filename $256. ;
run;