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

Hi,

 

I have a large list of datasets which I would like to compress in an automated manner. I tried the following:

 

 

1) Import a data set called Tables, listing the name of the tables to compress; 

2) Create a list of the names to be called by the macro;

 

proc sql noprint;
select distinct DATA_SET into :DATA_SET separated by ' ' from WORK.Tables;  
quit;

 

3) Define the macro

 

%LET DATASET=Tables;

 

4) Call the Macro

 

DATA Library.&DATASET (COMPRESS=CHAR);
SET Library.&DATASET;
RUN;

 

However it does not work

Can somebody please help me?

 

Thank you so much for your help.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

Where  did these datasets (DS1 DS2) store ?

My code assumed tables (DS1 DS2) stored at WORK library . and use PROC COPY copy them into another library X.

If you want pick up all the tables whose name starts with DS, you can do this .

option compress=char;
proc copy in=work out=x memtype=data noclone;
 select ds: ;
run;

If you want pick up all the tables whose name stored in table Tabelas .  Use a macro variable hold it, and put it into SELECT.

 

proc sql;
select data_set into : list separated by ' ' 
 from work.Tabelas;
quit;

option compress=char;
proc copy in=work out=x memtype=data noclone;
 select &list ;
run;

 

 

 

 

View solution in original post

13 REPLIES 13
Loko
Barite | Level 11

Hello,

 

it will help if you post the error you've got.

 

Also notice that in Sas there are 2 type of compressions :

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a001288760.htm

 

Escada
Obsidian | Level 7
It basically compresses the data set "Tables".
There is a problem in the macro...

##- Please type your reply above this line. Simple formatting, no
attachments. -##
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Please be more specific.  Post your code, post the problems from your log.  We can't see "a problem with the macro".  

As for your actual problem, why not just do (first two steps are just to get some test data in work it is the _null_ which is important):

data want1;
  set sashelp.class;
run;
data want2;
  set sashelp.cars;
run;
data _null_;
  set sashelp.vtable (where=(libname="WORK"));
  call execute(cat('data ',strip(memname),' (compress=char); set ',strip(memname),'; run;'));
run;
Escada
Obsidian | Level 7

Thank you again for your reply, RW9.

 

Now I changed a litle bit the code, embeding the library in the rows of the imported dataset, as follows:

 

proc sql print;
select distinct DATA_SET into :DATA_SET separated by ' ' from WORK.Tabelas;
quit;

 

%LET DATASET=TABELAS;

 

DATA &DATASET (COMPRESS=CHAR);
SET &DATASET;
RUN;

 

There is no error retrieved, however it compresses the WORK.Tabelas instead of the data sets contained in the rows of the table WORK.Tabelas.

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, two things.  Firstly you hjaven't used my code at all.  Secondly, your proc SQL is creating a list of distinct values, say there are three datasets DS1 DS2 DS3, that is the list provided to the macro variable, thus when the datastep is expanded it looks like this:

data ds1 ds2 ds3 (compress=char);
  set ds1 ds2 ds3;
run;

Which is not valid SAS code.  Use my code given, its far simpler.  Basically uses the datastep as a loop.  In your code, you would need some kind of loop to go over each dataset.

Escada
Obsidian | Level 7

You are right.

But I still get errors. Have a look:

 

Content of the Work.Tabelas:

libname

DS1

DS2

 

Code ran:

data _null_;
 set work.tabelas (where=(libname="work"));
   call execute(cat('data ',strip(memname),' (compress=char); set ',strip(memname),'; run;'));
run;

 

LOG:

data _null_;
 set work.tabelas (where=(libname="work"));/**/
   ERROR: Variable libname is not on file WORK.TABELAS.
 call execute(cat('data ',strip(memname),' (compress=char); set ',strip(memname),'; run;'));
run;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Sorry, your still not using my code.  Look in the library sashelp.  You will see a view called vtables.  This holds the table metadata, now I where clause that to find the datasets to process:

data _null_;
  set sashelp.vtable (where=(libname="WORK"));  
  call execute(cat('data ',strip(memname),' (compress=char); set ',strip(memname),'; run;'));
run;

The only bit you need to change is the where clause to point to the directory you are using, so say your dataset are in library ABC, change the "WORK" to "ABC".  

pearsoninst
Pyrite | Level 9
proc SQL ;
create table mytable as
select *
from sashelp.Air;
quit;
%Let x = mytable;
data work.abcd (compress = char);
set work.&x;
run;
it working i guess...can you pls check again and be more specific about the error
Escada
Obsidian | Level 7

Thanks for your reply, but still it compresses "mytable" and not the list of tables listed in "mytable"...

Ksharp
Super User

No need macro for such kind of task.

 


option compress=char;
proc copy in=work out=x memtype=data noclone;
run;

proc contents data=x._all_;
run;
Escada
Obsidian | Level 7

Thank you so much Ksharp.

I have difficulties to understand this code.

 

So, if I have:

 

My list of data sets to comprise stored in the data set Work.Tabelas, like:

 

data_set

DS1

DS2

 

where should I place it? 

Ksharp
Super User

Where  did these datasets (DS1 DS2) store ?

My code assumed tables (DS1 DS2) stored at WORK library . and use PROC COPY copy them into another library X.

If you want pick up all the tables whose name starts with DS, you can do this .

option compress=char;
proc copy in=work out=x memtype=data noclone;
 select ds: ;
run;

If you want pick up all the tables whose name stored in table Tabelas .  Use a macro variable hold it, and put it into SELECT.

 

proc sql;
select data_set into : list separated by ' ' 
 from work.Tabelas;
quit;

option compress=char;
proc copy in=work out=x memtype=data noclone;
 select &list ;
run;

 

 

 

 

Astounding
PROC Star

Just to expand the RW9 approach, spelling it out a bit ...

 

Start by creating a data set, rather than a macro variable:

 

proc sql;

create table table_list as select distinct DATA_SET from tables;

quit;

 

Then use that data set, generating a set of DATA steps:

 

data _null_;

set table_list;

call execute( 'data ' || strip(data_set) || ' (compress=char); set ' || strip(data_set) || '; run;');

run;

 

I omitted the Library. portion of that data set name, but that can easily be inserted if needed.

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
  • 13 replies
  • 2905 views
  • 6 likes
  • 6 in conversation