How to seperate dataset B based on list in dataset A.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

How to seperate dataset B based on list in dataset A.

I have two datasets; Dataset A will contain location with constant changing. Dataset B contain detail record and location is one of the column in there. Right now I need to split Dataset B based on location appear in Dataset A and export into Excel file and the filename must contain the location name. Example if Dataset A has 40 location, Dataset B needs to be split into 40 files and the filename will be Set_Town1, Set_Town2 ... Set_Town40.

I was thinking on populating Dataset A into array and do looping but 1 ) I dont know if it is consider the best approach  2) I dont know how to convert dataset into Array

I'm open to suggestion

I'm using EG 5.1.

Thanks in advance.


Accepted Solutions
Solution
‎12-19-2014 04:31 AM
Super User
Super User
Posts: 7,413

Re: How to seperate dataset B based on list in dataset A.

Hi,

Am presuming this will work ok in EG:

data location;
  location="UK"; output;
  location="DE"; output;
run;

data b;
  location="UK"; avar=3; output;
  location="UK"; avar=4; output;
  location="DE"; avar=1; output;
run;

options noquotelenmax;
data _null_;
  set location;
  call execute('ods tagsets.excelxp file="s:\temp\rob\Location '||strip(location)||'.xls" style=statistical;
                proc report data=b nowd split="~";
                  columns _all_;
                  where location="'||strip(location)||'";
                  define location / "Location" style(column)=[width=3cm];
                  define avar / "Result" style(column)=[width=2cm];
                run;
                ods tagsets.excelxp close;');
run;
options quotelenmax;

View solution in original post


All Replies
Solution
‎12-19-2014 04:31 AM
Super User
Super User
Posts: 7,413

Re: How to seperate dataset B based on list in dataset A.

Hi,

Am presuming this will work ok in EG:

data location;
  location="UK"; output;
  location="DE"; output;
run;

data b;
  location="UK"; avar=3; output;
  location="UK"; avar=4; output;
  location="DE"; avar=1; output;
run;

options noquotelenmax;
data _null_;
  set location;
  call execute('ods tagsets.excelxp file="s:\temp\rob\Location '||strip(location)||'.xls" style=statistical;
                proc report data=b nowd split="~";
                  columns _all_;
                  where location="'||strip(location)||'";
                  define location / "Location" style(column)=[width=3cm];
                  define avar / "Result" style(column)=[width=2cm];
                run;
                ods tagsets.excelxp close;');
run;
options quotelenmax;

Occasional Contributor
Posts: 6

Re: How to seperate dataset B based on list in dataset A.

Thank you so much. It works Smiley Happy

Respected Advisor
Posts: 3,899

Re: How to seperate dataset B based on list in dataset A.

Then please mark 's answer as correct. Firstly so the rest of us knows that it's answered and secondly because of

Occasional Contributor
Posts: 6

Re: How to seperate dataset B based on list in dataset A.

I encounter one problem, which is not enough memory due to the data size and number of split location. Any tips?

Super User
Posts: 9,687

Re: How to seperate dataset B based on list in dataset A.

How big table do you have ? Have you considered about splitting the table into small tables firstly ?

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 236 views
  • 4 likes
  • 4 in conversation