topic Re: PROC EXPORT-Observations in Dset to Tabs in SAS Procedures

PROC EXPORT-Observations in Dset to Tabs

robertrao — Tue, 18 Sep 2012 18:09:11 GMT

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

Re: PROC EXPORT-Observations in Dset to Tabs

Reeza — Tue, 18 Sep 2012 18:30:39 GMT

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;

Re: PROC EXPORT-Observations in Dset to Tabs

Linlin — Tue, 18 Sep 2012 18:55:08 GMT

Are the IDs numeric or character?

Re: PROC EXPORT-Observations in Dset to Tabs

Linlin — Tue, 18 Sep 2012 19:16:28 GMT

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;

Re: PROC EXPORT-Observations in Dset to Tabs

Reeza — Tue, 18 Sep 2012 19:33:32 GMT

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.

Re: PROC EXPORT-Observations in Dset to Tabs

Reeza — Tue, 18 Sep 2012 19:56:01 GMT

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

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

Re: PROC EXPORT-Observations in Dset to Tabs

Linlin — Tue, 18 Sep 2012 20:06:35 GMT

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;

Re: PROC EXPORT-Observations in Dset to Tabs

Linlin — Tue, 18 Sep 2012 20:30:27 GMT

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 ;

Re: PROC EXPORT-Observations in Dset to Tabs

robertrao — Wed, 19 Sep 2012 13:50:35 GMT

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;

Re: PROC EXPORT-Observations in Dset to Tabs

robertrao — Fri, 21 Sep 2012 15:25:05 GMT

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???

Re: PROC EXPORT-Observations in Dset to Tabs

Linlin — Fri, 21 Sep 2012 15:42:04 GMT

Hi,

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