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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

View solution in original post

14 REPLIES 14
art297
Opal | Level 21

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;

shivas
Pyrite | Level 9

Hi,

Try changing ...


data test.&dsn;

to

data test._&dsn;

Thanks,

Shiva

robertrao
Quartz | Level 8

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

art297
Opal | Level 21

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

art297
Opal | Level 21

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.

art297
Opal | Level 21

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.


robertrao
Quartz | Level 8

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

art297
Opal | Level 21

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

art297
Opal | Level 21

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

robertrao
Quartz | Level 8

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 ;

art297
Opal | Level 21

does the statement you submit really show:

FILENAME MyFile " my location"  ;


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


robertrao
Quartz | Level 8

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;

Linlin
Lapis Lazuli | Level 10

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;

Linlin
Lapis Lazuli | Level 10

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

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
  • 14 replies
  • 1240 views
  • 6 likes
  • 4 in conversation