Iterating through a SAS dataset and using variable for generating another dataset.

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

Iterating through a SAS dataset and using variable for generating another dataset.

Dear All,

I have a dataset "DataA" in which i have just have one variable named "NAME" and i have another dataset "DataB" in which i again have same variable along with plenty of other variable data.

Now i need to iterate thru DataA and create another dataset ExportDs and export this into excel file.

I absolutely have no idea on how this can be done.. Also note i'm not sure on the number of observations in DataA.

Request expert's advice on this... Thanks a lot in advance.

Ex

DataA

Name

AAA

BBB

CCC

DDD

NNN

DataB

Name  Age Class

AAA    11    1

AAA    12    2

AAA    13    3

BBB    12    2

CCC    13    3

CCC    14    5

ExportDS - AAA will have

AAA    11    1

AAA    12    2

AAA    13    3

ExportDS - BBB will have

BBB    12    2

ExportDS - CCC will have

CCC    13    3

CCC    14    5

Rgds, Anil


Accepted Solutions
Solution
‎03-12-2013 04:32 AM
Frequent Contributor
Posts: 81

Re: Iterating through a SAS dataset and using variable for generating another dataset.

Hi aj34321,

Please find below the code :-

===============================================================

data a;

input Name $;

datalines;

AAA

BBB

CCC

DDD

NNN

;

run;

data a;

set a;

obs=_n_;

run;

Data b;

input Name $  Age Class;

datalines;

AAA 11 1

AAA 12 2

AAA 13 3

BBB 12 2

CCC 13 3

CCC 14 5

;

run;

%macro dd();

proc sql;

select count(*) into:cnt from a;

quit;

%do i=1 %to &cnt.;

proc sql;

create table exportd&i as select b.* from b b , a a  where a.Name=b.Name and obs=&i.;

quit;

proc print data=exportd&i;

run;

%end;

%mend;

%dd;

==================================================================================

Output:-

=============================================================================

      The SAS System                          09:24 Tuesday, March 12, 2013  10

                                                    Obs    Name    Age    Class

                                                     1     AAA      11      1 

                                                     2     AAA      12      2 

                                                     3     AAA      13      3 

                                                           The SAS System                          09:24 Tuesday, March 12, 2013  11

                                                    Obs    Name    Age    Class

                                                     1     BBB      12      2 

                                                           The SAS System                          09:24 Tuesday, March 12, 2013  12

                                                    Obs    Name    Age    Class

                                                     1     CCC      13      3 

                                                     2     CCC      14      5 

==============================================================================

/Daman

View solution in original post


All Replies
Solution
‎03-12-2013 04:32 AM
Frequent Contributor
Posts: 81

Re: Iterating through a SAS dataset and using variable for generating another dataset.

Hi aj34321,

Please find below the code :-

===============================================================

data a;

input Name $;

datalines;

AAA

BBB

CCC

DDD

NNN

;

run;

data a;

set a;

obs=_n_;

run;

Data b;

input Name $  Age Class;

datalines;

AAA 11 1

AAA 12 2

AAA 13 3

BBB 12 2

CCC 13 3

CCC 14 5

;

run;

%macro dd();

proc sql;

select count(*) into:cnt from a;

quit;

%do i=1 %to &cnt.;

proc sql;

create table exportd&i as select b.* from b b , a a  where a.Name=b.Name and obs=&i.;

quit;

proc print data=exportd&i;

run;

%end;

%mend;

%dd;

==================================================================================

Output:-

=============================================================================

      The SAS System                          09:24 Tuesday, March 12, 2013  10

                                                    Obs    Name    Age    Class

                                                     1     AAA      11      1 

                                                     2     AAA      12      2 

                                                     3     AAA      13      3 

                                                           The SAS System                          09:24 Tuesday, March 12, 2013  11

                                                    Obs    Name    Age    Class

                                                     1     BBB      12      2 

                                                           The SAS System                          09:24 Tuesday, March 12, 2013  12

                                                    Obs    Name    Age    Class

                                                     1     CCC      13      3 

                                                     2     CCC      14      5 

==============================================================================

/Daman

Frequent Contributor
Posts: 76

Re: Iterating through a SAS dataset and using variable for generating another dataset.

Posted in reply to damanaulakh88

Thanks yaar.. This is what exactly i needed... You are the best...


Frequent Contributor
Posts: 81

Re: Iterating through a SAS dataset and using variable for generating another dataset.

No issues Smiley Happy And always remember to mark the answer as "Answered" when solved Smiley Happy

Frequent Contributor
Posts: 76

Re: Iterating through a SAS dataset and using variable for generating another dataset.

Posted in reply to damanaulakh88

Hey Daman,

One more query on this.. I want to export this data into excel file, so i need the file name as name.. meaning AAA.xls, BBB.xls.. how do i figure out this..?


Trusted Advisor
Posts: 1,137

Re: Iterating through a SAS dataset and using variable for generating another dataset.

Hi

you need to just include the proc export procedure in the macro as below

%macro dd();

proc sql;

select count(*) into:cnt from a;

quit;

%do i=1 %to &cnt.;

proc sql;

create table exportd&i as select b.* from b b , a a  where a.Name=b.Name and obs=&i.;

quit;

proc print data=exportd&i;

run;

proc export data=exportd&i outfile='path\exportd&i.xls' dbms=excel replace;

run;

%end;

%mend;

%dd;

Hope this helps

Thanks,

Jagadish

Thanks,
Jag
Frequent Contributor
Posts: 76

Re: Iterating through a SAS dataset and using variable for generating another dataset.

Posted in reply to Jagadishkatam

Actually the exported file will have a file name exportd1.xls, exportd2.xls, exportd3.xls.......

How will i get file names as ExportAAA.xls, ExportBBB.xls, ExportCCC.xls

Rgds, Anil

Trusted Advisor
Posts: 1,137

Re: Iterating through a SAS dataset and using variable for generating another dataset.

Please try the below code, check the highlighted portion

%macro dd();

proc sql;

select count(*) into:cnt from a;

select name into:cnt_1 - :cnt_%cmpres(&cnt) from a;

quit;

%do i=1 %to &cnt.;

proc sql;

create table exportd&i as select b.* from b b , a a  where a.Name=b.Name and obs=&i.;

quit;

proc print data=exportd&i;

run;

proc export data=exportd&i outfile="path\exportd&&cnt_&i..xls" dbms=excel replace;

run;

%end;

%mend;

%dd;

Thanks,

Jagadish

Thanks,
Jag
Frequent Contributor
Posts: 76

Re: Iterating through a SAS dataset and using variable for generating another dataset.

Posted in reply to Jagadishkatam

Hey Jagadish,

Thanks.. its working perfectly..

But again one problem, when the data is exported it exports in a default font & size which really looks very weird when this file is opened. So i need to format the entire file, i mean use ariel, 10 for all the cells.. I have a macro written already which works fine on open excel files, but in here, i need to know how do i open these files thru sas. I can open these files using X command but then it only works one by one..

So is there a way, that SAS auto formats these files for Ariel, 10 or all exported files open in a loop and i can execute my formating macro.

Thanks a lot and sorry for bugging you on this query, but trust me, your code has really simplified my BAU process and i need not write lengthy codes...

Thanks a lot again to you and Daman for helping me on this....

Trusted Advisor
Posts: 1,137

Re: Iterating through a SAS dataset and using variable for generating another dataset.

Hi

Please try the suggestion provided in the below link

https://communities.sas.com/thread/36211?start=0&tstart=0

Thanks,

Jagadish

Thanks,
Jag
🔒 This topic is solved and locked.

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

Discussion stats
  • 9 replies
  • 362 views
  • 1 like
  • 3 in conversation