topic Re: Linlin's Code Interpretation Please in SAS Procedures

Linlin's Code Interpretation Please

robertrao — Wed, 19 Sep 2012 16:17:23 GMT

BELOW iS LINLINS CODE FOR CONVERTING OBSERVATIONS DIRECTLY TO EXCEL TABS(WITHOUT THE INTERMEDIATE DATASET CREATION)

Dataset to be plugged in:MYDATASET

variable to keep:billnumber

Could you explain me the steps from 1-5 labelled in the bottom code.

What will go in place of 'id' in 1;

I guess sex in 1 is replaced by billnumber (then what about id???)

what will go in the place of n (in 2)

what is dsn in 5??

Thanks a ton

proc sort data=sashelp.class(keep=sex) out=class nodupkey;
by sex;
run;
/* 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 " my location" ;
DATA _NULL_ ;
rc = FDELETE('MyFile') ;
RUN ;

FILENAME MyFile CLEAR ;

libname test " my location";
%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;

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

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

FILENAME MyFile " my location" ;
DATA _NULL_ ;
rc = FDELETE('MyFile') ;
RUN ;
FILENAME MyFile CLEAR ;

libname test " my location";
%macro test;
%do i=1 %to &n;--------------------------------------------------------3
%let dsn=&&id&i; -----------------------------------------------------4
data test.&dsn;
set MYDATASET(where=(billnumber="&dsn"));-------------5
run;
%end;
%mend test;
%test
libname test clear;

Re: Linlin's Code Interpretation Please

art297 — Wed, 19 Sep 2012 17:07:22 GMT

Don't know if I'll have time to answer all of your questions but here is a start.

Step 1:

The use of proc sort with the nodupkey option and only keeping "sex", will create a file called "class" that only contains two records, one with "F" and one with "M" as the values for sex.

The first call symputx statement will create two macro variables, &id1 and &id2, with values of "F" and "M", respectively.

The second call symputx statement will create a macro variable, called "&m", that will have a value of "2"

Actually, it might be easiest for you to see what is going on if you set three system options before you run the code and see if you can answer your own questions by reading the resulting log.

run the following statement before running your code:

options mprint mlogic symbolgen;

Re: Linlin's Code Interpretation Please

shivas — Wed, 19 Sep 2012 17:33:04 GMT

Hi,

Try changing ...

data test.&dsn;

data test._&dsn;

Thanks,

Shiva

Re: Linlin's Code Interpretation Please

robertrao — Wed, 19 Sep 2012 17:47:13 GMT

Hi Shiva,

Your suggestion really worked well.

Could you also please explain me what is happening in this macro code below after Arthur had given the explanation to the intitial steps of the code;

How di dyou predict the correct answer would be after I put the _(underscore);

libname test "myfile";

%macro test;

%do i=1 %to &n;

%let dsn=&&id&i;

data test._&dsn;

set procedures_descrip2(where=(bill_no="&dsn"));

run;

%end;

%mend test;

%test

libname test clear;

Thanks a lot

Re: Linlin's Code Interpretation Please

art297 — Wed, 19 Sep 2012 17:59:35 GMT

That one I can answer: a filename, in SAS, must begin with either a letter or an underscore.

Re: Linlin's Code Interpretation Please

art297 — Wed, 19 Sep 2012 18:33:04 GMT

Again, since I didn't read all of the thread and don't know what the data really look like, I can only respond based on Shivas' comments. The code reads:

libname test " my location";

%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

It is just creating a loop that, in your case, is running the code between the %do and %end statements 26 times.

The %let statement is creating a macro variable which, from Shivas' comment and solution, is apparently equal to a number with leading zeros (e.g., 0000001). Then, the data step is run during each iteration creating files _000001, _000002, etc., simply setting your data file and using a where clause to only get those records that have the value that matches the specific number.

Re: Linlin's Code Interpretation Please

art297 — Wed, 19 Sep 2012 18:56:45 GMT

You didn't consider the effect of the initial proc sort. Linlin had suggested:

proc sort data=sashelp.class(keep=sex) out=class nodupkey;

by sex;

run;

That created a new file, called "class", that only contained two records, one for each sex. Thus, when class was read in, in the next step, there were only two records, thus _n_ was equal to 1 for F and 2 for M.

If you do the same thing with your data, you will initially create a file that only contains 26 records. Thus, when it is read in, in a similar second step, _n_ will be equal to 1 thru 26 during the iterations.

Re: Linlin's Code Interpretation Please

robertrao — Wed, 19 Sep 2012 19:33:31 GMT

I understand what you say...

dsn=&&id&i;

i have id1 thru id26 with the result of the step:

call symputx(cats('id',_n_),bill_no);

id1 has a value=000001

id2 has a value=000002

id3 has a value=000003 etc etc till id26

I WAS EXPECTING IT TO BE &idi INSTEAD OF &&id&i bcause :

when i=1 &idi I THOUGHT will get referenced to 000001(since id1 above has 000001)

when i=2 &idi I THOUGHT will get referenced to 000002 (since id2 above has 000002)

Could you explain me why we write the &i instead of only i???????

Thanks

Re: Linlin's Code Interpretation Please

art297 — Wed, 19 Sep 2012 19:59:46 GMT

When you use a %do loop the iteration variable, i in this case, by definition becomes a macro variable.

Re: Linlin's Code Interpretation Please

art297 — Thu, 20 Sep 2012 14:18:52 GMT

If you post a copy of the code you are actually running I (or someone else) can try to answer your question.

Re: Linlin's Code Interpretation Please

robertrao — Thu, 20 Sep 2012 14:32:31 GMT

This is the part of the code we discussed yesterday

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

FILENAME MyFile " my location" ;
DATA _NULL_ ;
rc = FDELETE('MyFile') ;
RUN ;

Re: Linlin's Code Interpretation Please

art297 — Thu, 20 Sep 2012 14:37:35 GMT

does the statement you submit really show:

FILENAME MyFile " my location" ;

or have you changed " my location" to something else?

Re: Linlin's Code Interpretation Please

robertrao — Sat, 22 Sep 2012 21:17:18 GMT

Hi Shiva Arthur and Team,

First of all Linlins code works fine for sashelp.class dataset(without the underscore).

but when it comes to my dataset I was not getting the result until I put the underscore(shiva suggested) in the below step (in italics):

Arthur corroborated saying that file names have to start with an underscore.

Also if the values for dsn are M and F :

data test.&dsn(talking about sas help.class dataset) is not creating anydatasets in the work library(i was thinking it would)....its directly creating

an Ecel file with the tabs M and F in the specified location.

1)WHY IS THE DATASETS NOT CREATED??????

2)WHY WAS I GETTING RESLUTS ONLY WHEN I INCLUDED AN UNDERSCORE WHEN IT WORKS FINE FOR SASHELP.CLASS WITHOUT PUTTING THE UNDERSCORE???????

ANY EFFORTS TO THESE TWO QUESTIONS ARE HIGHLY APPRECIATED...

libname test "C:\Data\Projects\HELP\tete.xls";
%macro test;

%do i=1 %to &n;
%let dsn=&&id&i;

data test.&dsn;                                               /* This is the step I am referring too */        i had to do datatest._&dsn
   set sashelp.class(where=(sex="&dsn"));
run;
%end;
%mend test;
%test
libname test clear;

Re: Linlin's Code Interpretation Please

Linlin — Sat, 22 Sep 2012 21:37:20 GMT

No datasets were created because you wanted excel file.

the modified code will create datasets for you.

you have to add "_" because you have IDs starting with numbers.

libname test "C:\Data\Projects\HELP\tete.xls";

%macro test;

%do i=1 %to &n;

%let dsn=&&id&i;

/* create sas dataset */

data _&dsn;

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

/* create excel file */

data test._&dsn; /* This is the step I am referring too */ i had to do datatest._&dsn

set _&dsn;

run;

%end;

%mend test;

%test

libname test clear;

Re: Linlin's Code Interpretation Please

Linlin — Sat, 22 Sep 2012 22:24:55 GMT

Yes. libname test "C:\Data\Projects\HELP\tete.xls"; created an excel file.