BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
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.
5 REPLIES 5
RickM
Fluorite | Level 6
I assume this is going in a macro? Use %scan().

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

%a
Ksharp
Super User
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
SASPhile
Quartz | Level 8
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;
Ksharp
Super User
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
Peter_C
Rhodochrosite | Level 12
> 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.

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
  • 5 replies
  • 785 views
  • 0 likes
  • 4 in conversation