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
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;
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;
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;
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.
I'm curious as to why you can't use SQL?
You can still do it, but it takes much more work...
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;
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 ;
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;
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???
Hi,
id1 and id2 are not variable values. They are macro variables. &id1 =F, &id2=M.
Are the IDs numeric or character?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.