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

Hi

I have this script of code where I have made som datasets beforehand. Afterwards I export them as CSV files and into their matching zipfiles (based on region codes). It has to be this way as it is an automatic code thats running everyday

The code now put the same 3 files in each zip folder for each region code - but now 2 of the region codes should have 3 more files/tables in the folder (table2,table4, table6) - only region code 1082 should have these files.

 

With the code below all regioncodes (1081, 1082, 1083, 1084, 1085) get the same files in their own zip file - I only want table 2, 4, 6 for code 1082.

How can I do that so ?

 

%macro leverance(region_code);

proc datasets;
delete table1-table6;
quit;


/*tabel 1*/

proc sql;
create table table1 as select 
datetime() as date format ymdhms.,
LAR,
EX,
ID,
TEST,
from basis_sample2
where region_adr=&region or region_lab=&region;
quit;


/*tabel2*/
data table2;
set AGE;
if region = &region;
run;


/*table3*/
proc sql;
create table table3 as select 
DATE format ymdhms.,
AGE,
ID,
CODE
from REG 
where regioncode=&region;
quit;


/*table4*/

data table4;
set municipality;
if region = &region;
run;


/*table5*/
data table5;
set VACC_data;
if region=&region;
run;


/*tabel 6*/

data table6;
set SEX;
if region = &region;
run;



%do i=1 %to 6;

proc export data=tabel&i. replace
  outfile="&out_folder_CSV.\&region._COVID19_TABLE&i..csv"
  dbms=dlm
;
delimiter=';';
quit;


/*make zip files****/
filename in "&out_folder_CSV.\&region._COVID19_TABLE&i..csv" lrecl=1 recfm=n;
filename out ZIP "&out_folder.\&region._COVID19_FOLDER.zip" member = "&region._COVID19_TABLE&i..sv" lrecl=1 recfm=n;

data _null_;
  rc = fcopy("in", "out");
  if rc ne 0 then 
    put "ERROR: for file &region._COVID19_RETUR_TABLE&i..csv";
run;

filename in clear;
filename out clear;
/***************/

%end;
%mend;



%macro dummy_macro();
%if &nobs ne 4 %then %do; 

filename mymail email from='TEST@TEST.dk' to=&mailto.
         subject='ERROR '
        

data _null_;
  file mymail;
  put 'TEST';
run;
%end;
%else %do;

%leverance(1081);
%leverance(1082);
%leverance(1083);
%leverance(1084);
%leverance(1085);

filename mymail email from='TEST@TEST.dk' to=&mailto.
         subject='data to the regions';

data _null_;
  file mymail;
  put 'SUCCES';
run;


%end;
%mend;

%dummy_macro();

 

1 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Onyx | Level 15

Maybe like this:

%macro leverance(region_code, tables = 1 3 5)/minoperator;

proc datasets;
delete table1-table6;
quit;


/*tabel 1*/

proc sql;
create table table1 as select 
datetime() as date format ymdhms.,
LAR,
EX,
ID,
TEST,
from basis_sample2
where region_adr=&region or region_lab=&region;
quit;


/*tabel2*/
data table2;
set AGE;
if region = &region;
run;


/*table3*/
proc sql;
create table table3 as select 
DATE format ymdhms.,
AGE,
ID,
CODE
from REG 
where regioncode=&region;
quit;


/*table4*/

data table4;
set municipality;
if region = &region;
run;


/*table5*/
data table5;
set VACC_data;
if region=&region;
run;


/*tabel 6*/

data table6;
set SEX;
if region = &region;
run;



%do i=1 %to 6;
%if &i. in (&tables.) %then
%do;
proc export data=tabel&i. replace
  outfile="&out_folder_CSV.\&region._COVID19_TABLE&i..csv"
  dbms=dlm
;
delimiter=';';
quit;


/*make zip files****/
filename in "&out_folder_CSV.\&region._COVID19_TABLE&i..csv" lrecl=1 recfm=n;
filename out ZIP "&out_folder.\&region._COVID19_FOLDER.zip" member = "&region._COVID19_TABLE&i..sv" lrecl=1 recfm=n;

data _null_;
  rc = fcopy("in", "out");
  if rc ne 0 then 
    put "ERROR: for file &region._COVID19_RETUR_TABLE&i..csv";
run;

filename in clear;
filename out clear;
/***************/
%end;
%end;
%mend;



%macro dummy_macro();
%if &nobs ne 4 %then %do; 

filename mymail email from='TEST@TEST.dk' to=&mailto.
         subject='ERROR '
        

data _null_;
  file mymail;
  put 'TEST';
run;
%end;
%else %do;

%leverance(1081);
%leverance(1082, tables = 1 2 3 4 5 6);
%leverance(1083);
%leverance(1084);
%leverance(1085);

filename mymail email from='TEST@TEST.dk' to=&mailto.
         subject='data to the regions';

data _null_;
  file mymail;
  put 'SUCCES';
run;


%end;
%mend;

%dummy_macro();

 

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

2 REPLIES 2
yabwon
Onyx | Level 15

Maybe like this:

%macro leverance(region_code, tables = 1 3 5)/minoperator;

proc datasets;
delete table1-table6;
quit;


/*tabel 1*/

proc sql;
create table table1 as select 
datetime() as date format ymdhms.,
LAR,
EX,
ID,
TEST,
from basis_sample2
where region_adr=&region or region_lab=&region;
quit;


/*tabel2*/
data table2;
set AGE;
if region = &region;
run;


/*table3*/
proc sql;
create table table3 as select 
DATE format ymdhms.,
AGE,
ID,
CODE
from REG 
where regioncode=&region;
quit;


/*table4*/

data table4;
set municipality;
if region = &region;
run;


/*table5*/
data table5;
set VACC_data;
if region=&region;
run;


/*tabel 6*/

data table6;
set SEX;
if region = &region;
run;



%do i=1 %to 6;
%if &i. in (&tables.) %then
%do;
proc export data=tabel&i. replace
  outfile="&out_folder_CSV.\&region._COVID19_TABLE&i..csv"
  dbms=dlm
;
delimiter=';';
quit;


/*make zip files****/
filename in "&out_folder_CSV.\&region._COVID19_TABLE&i..csv" lrecl=1 recfm=n;
filename out ZIP "&out_folder.\&region._COVID19_FOLDER.zip" member = "&region._COVID19_TABLE&i..sv" lrecl=1 recfm=n;

data _null_;
  rc = fcopy("in", "out");
  if rc ne 0 then 
    put "ERROR: for file &region._COVID19_RETUR_TABLE&i..csv";
run;

filename in clear;
filename out clear;
/***************/
%end;
%end;
%mend;



%macro dummy_macro();
%if &nobs ne 4 %then %do; 

filename mymail email from='TEST@TEST.dk' to=&mailto.
         subject='ERROR '
        

data _null_;
  file mymail;
  put 'TEST';
run;
%end;
%else %do;

%leverance(1081);
%leverance(1082, tables = 1 2 3 4 5 6);
%leverance(1083);
%leverance(1084);
%leverance(1085);

filename mymail email from='TEST@TEST.dk' to=&mailto.
         subject='data to the regions';

data _null_;
  file mymail;
  put 'SUCCES';
run;


%end;
%mend;

%dummy_macro();

 

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



Tom
Super User Tom
Super User

Do you need the work datasets?  Or do you just need the CSV files?

 

I would change the %DO loop to looping over a list of datasets. 

 

%let dslist=table1 table3 table5 ;

 

Then you can add a conditional to change the list of datasets based on the REGION value.

 

%if "&region" = "1082" %then %let dslist=&dslist table2 table4 table6;

Now the %DO loop could be:

 

 

%do i=1 %to %sysfunc(countw(&dslist));
  %let dsname=%scan(&dslist,&i);

And just replace TABLE&I with &DSNAME in the body of the %DO loop.

 

 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 2 replies
  • 746 views
  • 0 likes
  • 3 in conversation