topic Re: reading a variable list using INFILE in SAS Programming

reading a variable list using INFILE

taupirho — Mon, 22 Jul 2019 11:16:44 GMT

I currently use the following code to read a list of variable names

data list_of_tables ;
infile datalines;
input TABLE_NAME & $40.;
datalines;
TABLE_1
TABLE_2
;
run;

Lets say I had a variable number of TABLE_N's to read in like this ie in one run it would be 3, the next run it might be 5 etc ... . Is there a way to do this?

Re: reading a variable list using INFILE

PaigeMiller — Mon, 22 Jul 2019 12:13:11 GMT

I feel like I'm missing the point of your question. If you want to read in five table names, you would use this code. So what am I not understanding?


data list_of_tables ;
infile datalines;
input TABLE_NAME & $40.;
datalines;
TABLE_1
TABLE_2
TABLE_3
TABLE_4
TABLE_5
;
run;

Re: reading a variable list using INFILE

taupirho — Mon, 22 Jul 2019 12:18:46 GMT

Let's say I had large datasets that I wanted to divide up into smaller datasets and then process each new sub-set of data. On one run the sub-division might give me 3 sub-sets, TABLE_1, TABLE_2, TABLE_3 . On another run it might give me 100 sub-sets, TABLE_1-100 etc ...

Re: reading a variable list using INFILE

PaigeMiller — Mon, 22 Jul 2019 12:25:32 GMT

Are you trying to say that this is part of some larger activity, where sometimes there are 100 datasets and sometimes there are 600 datasets and sometimes there are 7 dataset, and you want this splitting to happen automatically? How would you know how many datasets there are? What are the rules to do the splitting?

Re: reading a variable list using INFILE

taupirho — Mon, 22 Jul 2019 12:54:51 GMT

Something like that. There are multiple datasets as input. Each input dataset is to be split up depending on its size.

Input dataset1 might end up being split up into 5 sub datasets, input dataset2 might be split up into 10 sub-datasets.

At the end of each input dataset splitting I'll know how many sub-sets it was split into. How do I then cycle through and get the name of each sub-set into a variable that I can use to process it.

Re: reading a variable list using INFILE

andreas_lds — Mon, 22 Jul 2019 12:56:59 GMT

And why do you have to split the dataset at all?

Splitting datasets requires additional code for splitting, even more code to process the fragments and another amount of code to add everything together at the end.

Re: reading a variable list using INFILE

PaigeMiller — Mon, 22 Jul 2019 12:59:30 GMT

@taupirho wrote:

Something like that. There are multiple datasets as input. Each input dataset is to be split up depending on its size.

Input dataset1 might end up being split up into 5 sub datasets, input dataset2 might be split up into 10 sub-datasets.

At the end of each input dataset splitting I'll know how many sub-sets it was split into. How do I then cycle through and get the name of each sub-set into a variable that I can use to process it.

So you know how many sub-sets it was split into, but how would any SAS code that we write know?

Re: reading a variable list using INFILE

PaigeMiller — Mon, 22 Jul 2019 13:01:08 GMT

@andreas_lds wrote:

And why do you have to split the dataset at all?

Splitting datasets requires additional code for splitting, even more code to process the fragments and another amount of code to add everything together at the end.

I agree with this, usually you would not want to do this.

The exception where you would want to do this is if you are parallelizing some very time consuming task, and you can run parts of this task on different remote computers simultaneously. So, @taupirho, are you in this situation, or not?

Re: reading a variable list using INFILE

taupirho — Mon, 22 Jul 2019 13:52:21 GMT

We are doing some numerical and frequency analysis a series of big datasets. A small data set would be 2.5 Gb and going up to 500Gb.

When I tried our analysis code on the one of the small datasets I got a memory error

Table XYZ (Number 1) Frequency Analysis began at Runtime 10:36:48. Rundate 11/07/2019.
Table XYZ (Number 1) has 537 Fields for Frequency Analysis
ERROR: PROC SUMMARY was terminated prematurely due to a memory shortage. Adjusting MEMSIZE and/or SUMSIZE may allow normal
processing to complete.
ERROR: The SAS System stopped processing this step because of insufficient memory.
WARNING: The data set WORK.SUMM1 may be incomplete. When this step was stopped there were 0 observations and 2 variables.

So one thought is to split up the larger datasets into smaller ones and do the analysis on each of the sub-sets

Re: reading a variable list using INFILE

PaigeMiller — Mon, 22 Jul 2019 14:02:36 GMT

@taupirho wrote:

We are doing some numerical and frequency analysis a series of big datasets. A small data set would be 2.5 Gb and going up to 500Gb.

When I tried our analysis code on the one of the small datasets I got a memory error

Table XYZ (Number 1) Frequency Analysis began at Runtime 10:36:48. Rundate 11/07/2019.
Table XYZ (Number 1) has 537 Fields for Frequency Analysis
ERROR: PROC SUMMARY was terminated prematurely due to a memory shortage. Adjusting MEMSIZE and/or SUMSIZE may allow normal
processing to complete.
ERROR: The SAS System stopped processing this step because of insufficient memory.
WARNING: The data set WORK.SUMM1 may be incomplete. When this step was stopped there were 0 observations and 2 variables.

So one thought is to split up the larger datasets into smaller ones and do the analysis on each of the sub-sets

I consider this to be a valid reason to split up the dataset, but you still need to explain how you know how many variables you have, and how they will be split.

PLEASE GIVE A REALISTIC EXAMPLE.

Re: reading a variable list using INFILE

Tom — Mon, 22 Jul 2019 15:47:02 GMT

If you want to supply a list of variable names that you can use in SAS code it might be easier to supply them in a macro variable.

%let varlist=
 AGE
 HEIGHT
 WEIGHT
;
proc summary data=sashelp.class ;
 var &varlist;
run;

If want to get a list of variables in an existing dataset then you can ask SAS to tell you.

proc contents data=have out=contents; run;

If you want to put a list of names into a macro variable from a set of names in a dataset then you can use the INTO clause of SQL query.

select name into :varlist separated by ' '
from contents;

Re: reading a variable list using INFILE

taupirho — Mon, 22 Jul 2019 16:05:39 GMT

Can you create a variable list "on the fly". Something like (forgive the syntax as I'm a newbie)

%let myvar=;

%do i 1 to 5;

myvar = myvar || "table_" &i.;

%let varlist = &myvar.;

Re: reading a variable list using INFILE

Tom — Mon, 22 Jul 2019 16:23:52 GMT

If you want a variable list where the names end in numbers then no need to list them all. Just use a variable list in your code.

proc means data=have;
  var table_1 - table_5 ;
run;

Re: reading a variable list using INFILE

PaigeMiller — Mon, 22 Jul 2019 16:39:08 GMT

If @Tom's reply doesn't quite answer your question, then please give a realistic example of the situation and what you expect.

Re: reading a variable list using INFILE

taupirho — Mon, 22 Jul 2019 16:47:17 GMT

Ok, I want to split some datasets into a number of smaller sub-sets. The number of sub-sets will vary depending on the size of the input dataset.

I then want to set up a loop for each sub-set and process it. I don't know in advance how many sub-sets there will be, only after the splitting has happened. In psuedocode,

Split dataset1

Produces 5 sub-sets say

for i = 1 to 5

process subset_i

Split dataset2

Produces 100 sub-sets say

for x = 1 to 100

process subset_x

Re: reading a variable list using INFILE

PaigeMiller — Mon, 22 Jul 2019 16:55:24 GMT

So you can't tell us how one example winds up with 5 subsets and another example winds up with 100 subsets? There must be a process.

But you can query a library to find out how many data sets exist, and obtain their names. Is that what you need?

Re: reading a variable list using INFILE

taupirho — Mon, 22 Jul 2019 17:00:37 GMT

Well if I was to spit up a 100 line dataset into subsets of 10 lines each ,
then split a 1000 line data set into subsets of ten lines each. That would
give me different numbers of subsets would it not?

Re: reading a variable list using INFILE

PaigeMiller — Mon, 22 Jul 2019 17:12:02 GMT

So, I'm not clear on your terminology, now that you bring up the 100 line dataset.

Exactly what are we talking about? Data sets? Variable names? Rows of a data set? Something else?

But this has gone on long enough, I'm still not clear about what the problem is, we're not making progress; and I request you start from the beginning, explain the entire problem IN DETAIL with examples of what the inputs are to this desired SAS program, and what the outputs are. Please use the proper terminology as well. Do not use "subset" as I don't know what that means in this context.

Re: reading a variable list using INFILE

ballardw — Mon, 22 Jul 2019 20:25:33 GMT

@taupirho wrote:
Well if I was to spit up a 100 line dataset into subsets of 10 lines each ,
then split a 1000 line data set into subsets of ten lines each. That would
give me different numbers of subsets would it not?

If I wanted to split a bunch of data sets into, roughly, the same number of records this is the approach I would take. My example uses 100 records each. If you want a different number of lines then replace all of the 100 values in the code below with your number. Irregular numbers may be possible but you would need a different variable for each set for managing the multiple both in the SQL and the data step do loop.

proc sql;
   create table tableinfo as
   select libname, memname, nobs, ceil( nobs/100) as sets
   from dictionary.tables
   where libname='LIB' and memtype='DATA';
quit;

data _null_;   
   set tableinfo;
   file print;
   length str $ 200.;
   do i=1 to sets;
      newmemname=cats(memname,i);
      fobs= (i-1)*100+1;
      lobs = i*100;
      str = catx(' ','data', newmemname,';');
      put str;
      str = catx(' ','set', catx('.',libname,memname),'firstobs=',fobs,'obs=',lobs,';');
      put str;
      str='run;';
      put str;
   end;
run;

The proc sql gets the names of the data sets, the number of observations and calculates how many sets of 100 lines would be needed.

The LIBNAME stored in the dictionary.columns is upper case so consider that when putting your name in the code. You may be able to select desired set names to process but you haven't provided any rules so none suggested.

You did not indicate whether the new data sets went to the same library or not. I would likely recommend a different library. In which case this line

str = catx(' ','data', newmemname,';');

should be

str = catx(' ','data', catxI('.','newlib',newmemname,';');

The example writes code to the results window that you could copy to the editor and run or save for later. The File statement could be modified to write to a SAS program file. Or use Call Execute(str); instead of Put str; to submit lines for execution.

CAVEATS: If you have long data set names adding digits could create invalid names longer than 32 characters. Up to you to manage that case. If you have data sets with millions of lines a split by a small value then you increase the chance of this happening.

If you have data sets whose names end in digit already then you run a chance of creating duplicate names. If is possible you may want to consider using

      newmemname=cats(memname,'_',i);

remember that each additional character is more likely to get to the 32 character name limit.

Re: reading a variable list using INFILE

Tom — Mon, 22 Jul 2019 22:51:47 GMT

What code are you trying to run that is running out of memory? Perhaps we can fix that code instead of worrying about splitting the data? You might want to start a new thread explaining what analysis you are doing that needs so much memory.

Do you really have large datasets with hundreds of variables? Are you sure that analyzing just some of the variables at a time will help? Do you really need to split the data to do that? Why not just pass a list of the variables to use to the analysis program?

Or do you really have large datasets with so many observations? Are you sure that analyzing just some of the observations at a time will help? Do you really need to split the data to do that? Why not just pass in a WHERE condition (or FIRSTOBS=/OBS= combination) to process just some of the observations? Do you have a natural grouping variable (STATE, COUNTRY, STUDYID) that you could use to split the data into smaller parts? Is the data already sorted by that variable(s)? Could you just add BY group processing to your analysis to make it use less memory?