topic Re: Do loop to run calculations over all data in SAS library in New SAS User

Do loop to run calculations over all data in SAS library

gsisme — Thu, 13 Feb 2020 15:14:53 GMT

Hi,

I'm trying to find a piece of script that will apply some simple calculations to the entire contents of a SAS library (2,800 files, all of which have the same columns, and are named "_YYYYMMDD").

The calculation itself is straight forward (create a column based on an existing one, change any char values to numeric, and store new column as numeric). I've built this, however I can't find any method of running this on the contents of the library.

I've created a Dictionary which lists all data within the library as follows:

proc sql ;
create table work.mytables as
select *
from dictionary.tables
where libname = 'B'
order by memname ;
quit ;

Anyone have any ideas how to proceed from here? Thanks.

Using SAS EG8.1

Re: Do loop to run calculations over all data in SAS library

PaigeMiller — Thu, 13 Feb 2020 15:25:35 GMT

Are these .txt files? Are these .csv files? Can you give us more information? How are you reading them?

If they are .txt files and maybe if they are .csv files, you can read them all with one SAS data step.

Re: Do loop to run calculations over all data in SAS library

gsisme — Thu, 13 Feb 2020 15:27:01 GMT

Apologies, I've imported them to the SAS library and they're stored as SAS files.

Re: Do loop to run calculations over all data in SAS library

yabwon — Thu, 13 Feb 2020 15:29:28 GMT

Hi @gsisme ,

Try CALL EXECUTE():

data testSet1 testSet2;
  variable = 42;
run;

data YOUR_DICTIONARY;
  datasetnameVariable = 'testSet1'; output;
  datasetnameVariable = 'testSet2'; output;
run;


filename t TEMP;

data _null_;
file t;
input;
put _infile_;
cards4;
/* put your code here */
/*e.g.*/
  
  variable = 100 * variable;
  
/* put your code here */
;;;;
run;

data _null_;
  set YOUR_DICTIONARY;
    call execute('data ' || datasetnameVariable || ';'); 
    call execute('set ' || datasetnameVariable || ';'); 
    call execute('%nrstr(%include t;) run;'); 
run;

All the best

Bart

Re: Do loop to run calculations over all data in SAS library

yabwon — Thu, 13 Feb 2020 15:32:39 GMT

Other option would be to use %macro instead filename,

%macro code();
/* put your code here */
/*e.g.*/
  
  variable = 100 * variable;
  
/* put your code here */
%mend code;

data _null_;
  set YOUR_DICTIONARY;
    call execute('data ' || datasetnameVariable || ';'); 
    call execute('set ' || datasetnameVariable || ';'); 
    call execute('%nrstr(%code()) run;'); 
run;

All the best

Bart

Re: Do loop to run calculations over all data in SAS library

PaigeMiller — Thu, 13 Feb 2020 15:39:39 GMT

@gsisme wrote:

Apologies, I've imported them to the SAS library and they're stored as SAS files.

If they WERE .txt files or .csv files, you still wouldn't need a loop and you wouldn't need the individual SAS data sets. You could read them all with a single DATA step.

Re: Do loop to run calculations over all data in SAS library

ballardw — Thu, 13 Feb 2020 15:46:25 GMT

@gsisme wrote:

Apologies, I've imported them to the SAS library and they're stored as SAS files.

I'm going to take this as meaning that you used proc import. Did you check the status of the variables as they were imported?

Did you import some sort of text files such as delimited with spaces, commas, tabs or other characters? Were any of these files supposed to have the same structure (same variables, in same order?).

If so then you really need to read the data with data step code to control the contents. Especially if you had a description document that would indicate what the maximum length of variables would be and the purpose of variables.

Prevention by reading properly in the first place is much better than trying to fix things later.

Also you can add the calculation to the step that reads the data.

I would be extremely cautious of any process that does what you ask because it has the potential to cause other issues. There many types of identifiers used that contain only digits but should not be treated as numeric. Sometimes account "numbers" will exceed the length that SAS will store properly. And account numbers should not have any numeric calculations done with them in general.

Re: Do loop to run calculations over all data in SAS library

Reeza — Thu, 13 Feb 2020 16:44:39 GMT

https://communities.sas.com/t5/SAS-Programming/Run-data-step-on-all-files-in-directory/m-p/624517#M183993

Is this homework or something?

Re: Do loop to run calculations over all data in SAS library

Reeza — Thu, 13 Feb 2020 16:48:13 GMT

You started at the wrong place, first figure out how you do what you need for one file and then you generalize that.

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

To automate the task once you have it working you can either use a macro or combine the datasets and do it all at once or several different options but it depends a lot on what you're trying to do overall.

Re: Do loop to run calculations over all data in SAS library

gsisme — Thu, 13 Feb 2020 16:50:07 GMT

Thanks Bart, did exactly what needed.