BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gsisme
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



View solution in original post

9 REPLIES 9
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
gsisme
Obsidian | Level 7

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

PaigeMiller
Diamond | Level 26

@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.

--
Paige Miller
ballardw
Super User

@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.

yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



gsisme
Obsidian | Level 7
Thanks Bart, did exactly what needed.
Reeza
Super User
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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 899 views
  • 3 likes
  • 5 in conversation