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

Hey all. Currently working on a (hopefully) brief program. Here's what it needs to do (my code follows):

1. Go to a DB2 database and check the names of all data sets contained therein.

2. In these data sets, check for a common variable (call it var1).

3. With this common variable (var1), check the values.

4. If the value is 1, output to one data set. If 1.2, output to another. If there are values of 1 and 1.2 in a data set, then output to a third data set.

The thing is, there are quite a few data sets. I've got it to pick out the specific library, then pick out the tables with the variable name I'm looking for. However, I can't get it to properly check the value of this variable and then output accordingly. My code is attached. Some code is commented out. Those bits were experimental. Thought I'd leave them in so you could see my thought process.

Thanks guys!


1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Jonathon,

I'm assuming you are properly extracting all the relevant data set names into the SAS data set CallAllLibname (if not, we can revisit that piece).  What your program will need to do is actually examine the data within each data set to determine its final destination.  Here's one way to do that.

I'm altering the goal, slightly, to create three macro variables:

&ONE holding a list of all data set names with 1 only

&ONEptTWO holding a list of all data set names with 1.2 only

&ONEandONEptTWO holding a list of all data set names holding both values

proc sql noprint;

   select trim(tbname) into : dataset_list from callalllibname;

   %let nobs = &sqlobs;

quit;

%global ONE ONEptTWO ONEandONEptTWO;

%let ONE=;

%let ONEptTWO=;

%let ONEandONEptTWO=;

%macro test_each;

   %local i next_dataset;

   %do i=1 %to &nobs;

          %let next_dataset = %scan(&dataset_list, &i, %str( ));   /* get name of next data set in the list */

          data _null_;

              do until (one='Y' and onepttwo='Y') or alldone;

                   set &next_dataset (keep=var1) end=alldone;

                   if var1=1 then one='Y';

                   else if var1=1.2 then onepttwo='Y';

             end;

             if one='Y' and onepttwo='Y' then call execute("%let ONEandONEptTWO = &ONEandONEptTWO &next_dataset;");

             else if one='Y' then call execute("%let ONE = &ONE &next_dataset;");

             else if onepttwo='Y' then call execute("%let ONEptTWO = &ONEptTWO &next_dataset;");

          run;

   %end;

%mend;

%test_each

%put _user_;

If this is getting the information you need, but in a somewhat different format, we can adjust it.  But first things first.

The code is untested, so there is some chance that we might need to debug it.

Good luck.

View solution in original post

36 REPLIES 36
jtrousd
Calcite | Level 5

I'd very much appreciate any fresh ideas. I've now been fumbling through this for the last five hours with minimal changes. If any of you folks could provide even a modicum of insight, that'd be great. Just to clarify exactly what I'm trying to do.

1. I have 300+ data sets. All of them have a common variable (var1)

2. This common variable takes on two values: 1 and 1.2.

3. In some data sets, it only has value 1. In others, it only has value 1.2. Still in others, it has value 1 and 1.2.

4. I'd like to find the data sets which have a value of 1 only and output the data set names to another data set. For example, if the data sets PIG, COW, and DOG all have var1 = 1.0, then I want SAS to take the the data set names PIG, COW, and DOG and output them to a fourth data set... call it ONE.

5. If the data sets BEAR, MOM, CAR all have var1 = 1.2, then I want SAS to output the data set names to a separate data set, call it ONEptTWO.

6. Lastly, if the data sets COOK, PAW, TIRE all have var1 = 1.0 or var1 = 1.2 (hundreds of rows in all of these data sets, so some are 1.0 and some are 1.2), then I'd like SAS to ouput the data set names to a separate data set, call it ONEandONEptTWO.

Since there are so many data sets (300+), I don't want to have to insert the data set names separately. This is why I'm attempting to use SQL with a macro.

Thanks,

Jonathon

Astounding
PROC Star

Jonathon,

I'm assuming you are properly extracting all the relevant data set names into the SAS data set CallAllLibname (if not, we can revisit that piece).  What your program will need to do is actually examine the data within each data set to determine its final destination.  Here's one way to do that.

I'm altering the goal, slightly, to create three macro variables:

&ONE holding a list of all data set names with 1 only

&ONEptTWO holding a list of all data set names with 1.2 only

&ONEandONEptTWO holding a list of all data set names holding both values

proc sql noprint;

   select trim(tbname) into : dataset_list from callalllibname;

   %let nobs = &sqlobs;

quit;

%global ONE ONEptTWO ONEandONEptTWO;

%let ONE=;

%let ONEptTWO=;

%let ONEandONEptTWO=;

%macro test_each;

   %local i next_dataset;

   %do i=1 %to &nobs;

          %let next_dataset = %scan(&dataset_list, &i, %str( ));   /* get name of next data set in the list */

          data _null_;

              do until (one='Y' and onepttwo='Y') or alldone;

                   set &next_dataset (keep=var1) end=alldone;

                   if var1=1 then one='Y';

                   else if var1=1.2 then onepttwo='Y';

             end;

             if one='Y' and onepttwo='Y' then call execute("%let ONEandONEptTWO = &ONEandONEptTWO &next_dataset;");

             else if one='Y' then call execute("%let ONE = &ONE &next_dataset;");

             else if onepttwo='Y' then call execute("%let ONEptTWO = &ONEptTWO &next_dataset;");

          run;

   %end;

%mend;

%test_each

%put _user_;

If this is getting the information you need, but in a somewhat different format, we can adjust it.  But first things first.

The code is untested, so there is some chance that we might need to debug it.

Good luck.

jtrousd
Calcite | Level 5

Thanks for the input, Astounding! I'll give it a go and get right back to you.

Astounding
PROC Star

Small changes to make:

the INTO : clause will need "separated by" added, and adding DISTINCT can't hurt:

select distinct(trim(dbname)) into : dataset_list separated by ' ' from CallAllLibname;


jtrousd
Calcite | Level 5

Similar error as before. I am getting some slight output now, though. Gives me hope :smileygrin:

jtrousd
Calcite | Level 5

Alright. A good bit of it seemed to run properly. The log file was a little less clear than I had hoped from the PUT statement, but that could have something to do with what SAS considers a small error near the beginning. I'm attaching the portion of the log which contains this error.

I am able to pick out the data set names correctly. This is verified when I run %PUT &VARLIST. They're all there.

Astounding
PROC Star

You don't show what came before the error.  I suspect there is a missing semicolon, so SAS doesn't see PROC SQL as a separate statement.

jtrousd
Calcite | Level 5

Hmmm. Doesn't look like any semi-colons missed on my end. Shouldn't be anything after the &ONE, &ONEptTWO, and &ONEandONEptTWO statements should there?

Astounding
PROC Star

The descriptions of &ONE, &ONEptTWO, &ONEand&ONEptTWO should not be part of the program.  They're just describing the plan.  The SAS code begins with the %GLOBAL statement, followed by three %LET statements.  Each of those should have its own semicolon.

jtrousd
Calcite | Level 5

I think the program is executing properly because I'm now getting the same error message many, many times in one run. It has to do with the library it's attempting to pull the datasets from. For instance, a common issue is that it's stating:

ERROR: File WORK.dataset.DATA does not exist.


ERROR 79-322: Expecting a ;.

ERROR 76-322: Syntax error, statement will be ignored.

This is what happens for each attempt the program makes.

EDIT: Should mention that I'm looking for a specific library name. We'll call it BAGS. So all data sets will be of the form BAGS.dataset

Astounding
PROC Star

OK, data sets not existing means they are not in the WORK folder.  The SET statement should have a two-part name, like:

set libname.&next_dataset;

Whatever LIBNAME you have used, apply it here.

As far as the syntax error goes, perhaps you are missing a semicolon within the DATA step.  If adding the LIBNAME doesn't fix it all, try posting your code.

jtrousd
Calcite | Level 5

Maybe I'm missing the semicolon, but here's my program:

jtrousd
Calcite | Level 5

On my way home for the day... wife's birthday and all. I appreciate all of your help. SQL and Macros are new for me, so this has been a great learning experience. Will check in tomorrow to see if you've replied.

Astounding
PROC Star

Nothing obvious here.  Let's get a few diagnostics.  Move the %put _user_ statement to just before the %mend statement.  Make sure OPTIONS MPRINT; is in place, as well.  That's will probably give enough to work with.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 36 replies
  • 1549 views
  • 14 likes
  • 3 in conversation