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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
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: 75

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

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: 75

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

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,129

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: 75

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

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,129

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: 75

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

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,129

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.

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

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