BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
robertrao
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

10 REPLIES 10
Reeza
Super User

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;

Linlin
Lapis Lazuli | Level 10

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;

Reeza
Super User

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.

Reeza
Super User

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

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

Linlin
Lapis Lazuli | Level 10

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;

Linlin
Lapis Lazuli | Level 10

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 ;

robertrao
Quartz | Level 8

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;

robertrao
Quartz | Level 8

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

Linlin
Lapis Lazuli | Level 10

Hi,

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

Linlin
Lapis Lazuli | Level 10

Are the IDs numeric or character?

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!

What is Bayesian Analysis?

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.

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
  • 10 replies
  • 1072 views
  • 6 likes
  • 3 in conversation