topic Re: Automate in SAS Programming

Automate

SASPhile — Fri, 19 Nov 2010 20:56:44 GMT

Automation to create datasets from excel files as shown below:They are all saved in a folder.
feed_cvs_cph_20101031.xls
feed_walg_cph_20101031.xls
feed_apo_cph_20101031.xls

So if SAS reads feed_cvs_cph_20101031.xls, dataset cvs is to be created.
i.e words between first two undercores are dataset names.

Re: Automate

RickM — Fri, 19 Nov 2010 21:05:33 GMT

I assume this is going in a macro? Use %scan().

%macro a;
%put %scan("feed_cvs_cph_20101031.xls",2,_);
%mend;

%a

Re: Automate

Ksharp — Mon, 22 Nov 2010 03:21:39 GMT

Hi.I found the code I writed before.
But not test it, You can refer to it.

[pre]
%let subdir=D:\sasdata\;
filename dir pipe "dir &subdir.*.xls /B";
data new;
infile dir truncover end=last;
input filename $100.;
filename=cats("&subdir",filename);
call symputx(cats('path',_n_),filename);
call symputx(cats('dsn',_n_),scan(scan(filename,3,'\'),2,'_'));
if last then call symputx('nobs',_n_);
run;
%put _user_;
%macro import;
%do i=1 %to &nobs;
proc import datafile="&&path&i" out=&&dsn&i dbms=excel replace;
getnames=yes;
run;
%end;
%mend import;

%import
[/pre]

Ksharp

Re: Automate

SASPhile — Tue, 23 Nov 2010 15:03:33 GMT

Ksharp...That is a sharp solution.Thanks.
The export procedure below creates worksheets whether or not qty is greater than 100.if it is not only a blank sheet is created or else only those records that are greater than 100 are created.How to create only those work sheets where qty is greater than 100.say for instance there are 10 datasets and only 3 of them meet the where condition, we need only those 3 .no need to output blank worksheets for other 7.
proc export data=test&i.(where=(lqty>=100))
outfile='H:\PJ\Outliers\Outfiles\outliers-%sysfunc(date(),mmddyyd10.).xls'
dbms=excel2000
replace;
SHEET=%scan("&&path&i",2,_);
run;

Re: Automate

Ksharp — Wed, 24 Nov 2010 03:12:08 GMT

Sorry.I have no clue about this .
Maybe you need to judge the number of observations of sub-dataset to decide to call macro.

[pre]
proc sql;
select nobs
into :_nobs
from dictionary.tables
where libname ='WORK' and memname='TABLENAME';quit;
[/pre]
if &nobs ne 0 then ' proc export '

Ksharp Message was edited by: Ksharp

Re: Automate

Peter_C — Wed, 24 Nov 2010 16:29:50 GMT

> The export procedure below creates worksheets whether
> or not qty is greater than 100.if it is not only a
> blank sheet is created or else only those records
> that are greater than 100 are created.How to create
> only those work sheets where qty is greater than
> 100.say for instance there are 10 datasets and only 3
> of them meet the where condition, we need only those
> 3 .no need to output blank worksheets for other 7.
> proc export data=test&i.(where=(lqty>=100))
>
> utfile='H:\PJ\Outliers\Outfiles\outliers-%sysfunc(date
> (),mmddyyd10.).xls'
> dbms=excel2000
> replace;
> SHEET=%scan("&&path&i",2,_);
> ;

package the process (with macro or call execute) to first check whether there are relevant observations, and only when any are found, launch the step to write to excel.
For example:
%let age = 25 ;
data _null_ ;
set sashelp.class ;
where age > &age ;
call execute( '
proc print data= sashelp.class;
where age>&age;
run ;
');
stop ;
run;
There are no obs in sashelp.class with age > 16 so for 25 the proc print syntax will not be "executed".
when you try it with
%let age=15 ;
you get a row through the where clause and that causes the proc print syntax to be submitted.

You can replace the proc print syntax with your proc report syntax.