Help using Base SAS procedures

PROC EXPORT-Observations in Dset to Tabs

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,040
Accepted Solution

PROC EXPORT-Observations in Dset to Tabs

ExcelHi Team.

I have a dataset with approx 14000 rows(these 14000) records have info about 25 patients only.....

For example You can say approc that each patient has 14000/25 records .

Each patient has an unique ID

How can I export each patients information to seperate TAB in Excel.

Could anyone help me with the code please.......

I know the code for exporting datasets to Excel Tabs.......I am confused with the current situation....

Thanks


Accepted Solutions
Solution
‎09-18-2012 04:06 PM
Super Contributor
Posts: 1,636

Re: PROC EXPORT-Observations in Dset to Tabs

try this one:

proc sort data=sashelp.class(keep=sex) out=class nodupkey;
by sex;
/* create macro variables */
data _null_;
   set class end=last ;
   call symputx(cats('id',_n_),sex);
   if last then call symputx('n',_n_);

/* delete the file in case you run the code more than once */
FILENAME MyFile "C:\temp\forum\tete.xls"  ;
  DATA _NULL_ ;
    rc = FDELETE('MyFile') ;
  RUN ;
FILENAME MyFile CLEAR ;

libname test "c:\temp\forum\tete.xls";

%macro test;
%do i=1 %to &n;
%let dsn=&&id&i;
data test.&dsn;
   set sashelp.class(where=(sex="&dsn"));
run;
%end;
%mend test;
%test

libname test clear;

View solution in original post


All Replies
Super User
Posts: 17,850

Re: PROC EXPORT-Observations in Dset to Tabs

There are many ways and I'm sure others will suggest some but ODS is a simple way. Cynthia posted a solution about that on here in the past few weeks.

This paper covers one way though using ODS if you don't mind the XML file as an output.

proc sort data=sashelp.class out=class; by sex; run;

ods tagsets.excelxp file='C:\temp\sample_out.xls';

proc print data=class;

by sex;

run;

ods tagsets.excelxp close;

Super Contributor
Posts: 1,636

Re: PROC EXPORT-Observations in Dset to Tabs

Hi,

you can try to modify(the red parts only) the sample code:

proc sql noprint;
  select distinct sex into :Ids separated by ' ' from sashelp.class;
quit;

libname test "c:\temp\forum\tete.xls";
%macro test;
%do i=1 %to %sysfunc(countw(&ids));
%let dsn=%sysfunc(scan(&ids,&i));
data test.&dsn;
   set sashelp.class(where=(sex="&dsn"));
run;
%end;
%mend test;
%test

libname test clear;

Super User
Posts: 17,850

Re: PROC EXPORT-Observations in Dset to Tabs

Its one way, you need to run both parts.

The SQL creates the list of by variables to loop over and export one at a time in the macro below.

Super User
Posts: 17,850

Re: PROC EXPORT-Observations in Dset to Tabs

I'm curious as to why you can't use SQL?

You can still do it, but it takes much more work...

Solution
‎09-18-2012 04:06 PM
Super Contributor
Posts: 1,636

Re: PROC EXPORT-Observations in Dset to Tabs

try this one:

proc sort data=sashelp.class(keep=sex) out=class nodupkey;
by sex;
/* create macro variables */
data _null_;
   set class end=last ;
   call symputx(cats('id',_n_),sex);
   if last then call symputx('n',_n_);

/* delete the file in case you run the code more than once */
FILENAME MyFile "C:\temp\forum\tete.xls"  ;
  DATA _NULL_ ;
    rc = FDELETE('MyFile') ;
  RUN ;
FILENAME MyFile CLEAR ;

libname test "c:\temp\forum\tete.xls";

%macro test;
%do i=1 %to &n;
%let dsn=&&id&i;
data test.&dsn;
   set sashelp.class(where=(sex="&dsn"));
run;
%end;
%mend test;
%test

libname test clear;

Super Contributor
Posts: 1,636

Re: PROC EXPORT-Observations in Dset to Tabs

if your id variable is numeric then

data test.&dsn;

   set sashelp.class(where=(sex="&dsn"));

run;

would be:

data test.&dsn;

   set sashelp.class(where=(sex=&dsn));

run;

The " " is not needed when compare numeric variable.

the codes below delete the excel file if it already exist.

FILENAME MyFile "C:\temp\forum\tete.xls"  ;

  DATA _NULL_ ;

    rc = FDELETE('MyFile') ;

  RUN ;

FILENAME MyFile CLEAR ;

Super Contributor
Posts: 1,040

Re: PROC EXPORT-Observations in Dset to Tabs

Hi LInlin,

I tried to apply your code.But I got confused about what to put on the id?  leave it like that?

My unique variable which identifies patient is bill number this time....

could you also explain me the callsymput step and the bottom macro please???

dsn=&&id&i; -------i dint get that too

Thanks

proc sort data=MyDATASET(keep=bill_no) out=classify nodupkey;
by bill_no;
run;
/* create macro variables */
data _null_;
   set classify end=last ;
   call symputx(cats('id',_n_),bill_no);
   if last then call symputx('n',_n_);

/* delete the file in case you run the code more than once */

FILENAME MyFile "C:\Data\Projects\sample_out.xls"  ;
  DATA _NULL_ ;
    rc = FDELETE('MyFile') ;
  RUN ;
FILENAME MyFile CLEAR ;

libname test "C:\Data\Projects\sample_out.xls";
%macro test;
%do i=1 %to &n;
%let dsn=&&id&i;
data test.&dsn;
   set MyDATASET(where=(bill_no="&dsn"));
run;
%end;
%mend test;
%test
libname test clear;

Super Contributor
Posts: 1,040

Re: PROC EXPORT-Observations in Dset to Tabs

Hi Team,

I have had enough of discussion on this code but I feel I still lack some understanding .

in the final macro step we set sashelp.class and requested the condition(where sex="&dsn");

&dsn gets resoved to id1 and  id2 according to the steps done above the macro.

So MY QUESTION IS THAT" WE DONT HAVE A VALUE id1 and id2 so on .........under the variable sex in sashelp.class.

Can someone help me understand???

Super Contributor
Posts: 1,636

Re: PROC EXPORT-Observations in Dset to Tabs

Hi,

id1 and id2 are not variable values. They are macro variables. &id1 =F, &id2=M.

Super Contributor
Posts: 1,636

Re: PROC EXPORT-Observations in Dset to Tabs

Are the IDs numeric or character?

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 375 views
  • 6 likes
  • 3 in conversation