Help using Base SAS procedures

Linlin's Code Interpretation Please

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

Linlin's Code Interpretation Please

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;


Accepted Solutions
Solution
‎09-19-2012 01:59 PM
PROC Star
Posts: 7,363

Re: Linlin's Code Interpretation Please

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

View solution in original post


All Replies
PROC Star
Posts: 7,363

Re: Linlin's Code Interpretation Please

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

Step 1:

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_);

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;

Super Contributor
Posts: 349

Re: Linlin's Code Interpretation Please

Hi,

Try changing ...


data test.&dsn;

to

data test._&dsn;

Thanks,

Shiva

Super Contributor
Posts: 1,040

Re: Linlin's Code Interpretation Please

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

Solution
‎09-19-2012 01:59 PM
PROC Star
Posts: 7,363

Re: Linlin's Code Interpretation Please

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

PROC Star
Posts: 7,363

Re: Linlin's Code Interpretation Please

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.

PROC Star
Posts: 7,363

Re: Linlin's Code Interpretation Please

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.


Super Contributor
Posts: 1,040

Re: Linlin's Code Interpretation Please

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

PROC Star
Posts: 7,363

Re: Linlin's Code Interpretation Please

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

PROC Star
Posts: 7,363

Re: Linlin's Code Interpretation Please

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

Super Contributor
Posts: 1,040

Re: Linlin's Code Interpretation Please

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 ;

PROC Star
Posts: 7,363

Re: Linlin's Code Interpretation Please

does the statement you submit really show:

FILENAME MyFile " my location"  ;


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


Super Contributor
Posts: 1,040

Re: Linlin's Code Interpretation Please

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;

Super Contributor
Posts: 1,636

Re: Linlin's Code Interpretation Please

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;

Super Contributor
Posts: 1,636

Re: Linlin's Code Interpretation Please

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

☑ This topic is SOLVED.

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

Discussion stats
  • 14 replies
  • 442 views
  • 6 likes
  • 4 in conversation