DATA Step, Macro, Functions and more

Loop Through Multiple Datasets and Create Field based on Dataset Name?

Accepted Solution Solved
Reply
Regular Contributor
Posts: 152
Accepted Solution

Loop Through Multiple Datasets and Create Field based on Dataset Name?

Hello

 

I am using SAS 9.4.  I have a dataset of wide data i.e. chart_num, acct_num, disch_date, A01, A02, A03 etc.  I created datasets from if A01 > 0 then dataset A01 etc. Now I'd like to loop through the datasets and assign a field of HH = dataset name so I can merge them together.  Therefore dataset A01 will have a field of HH with "A01" and dataset A02 will have a field of HH with "A02" etc. but do this programatically.

 

How would I do this?   Thanks.


Accepted Solutions
Solution
‎01-12-2017 12:20 PM
Super User
Super User
Posts: 7,401

Re: Loop Through Multiple Datasets and Create Field based on Dataset Name?

Here are two options:

data test_grp;
input @1 chartno $5.
               @6 acctno  $5.
               @11 A01            $1.
               @12 A02            $1.
               @13 A03            $1.
               @14 A04    $1.;
 
cards;
11111222220101
22222333331000
33333444440010
44444555551100
55555666660010
run;

data want (drop=a0:);
  set test_grp;
  array vars{*} a0:;
  do i=1 to dim(vars);
    if vars{i}="1" then do;
      actual=vname(vars{i});
      output;
    end;
  end;
run;

/* Option 2 */
proc transpose data=test_grp out=want2;
  by chartno acctno;
  var a0:;
run;
data want2;
  set want2 (where=(col1="1"));
run;

View solution in original post


All Replies
Super User
Posts: 10,500

Re: Loop Through Multiple Datasets and Create Field based on Dataset Name?

I would think this would be much easier to do in the orginal split.

Instead of the code line you showed as: if A01 > 0 then dataset A01 ;

use

if A01 > 0 then do;

   HH='A01';

   output A01;

end;

Frequent Contributor
Frequent Contributor
Posts: 89

Re: Loop Through Multiple Datasets and Create Field based on Dataset Name?

So you have data together, you split it apart, and merge it back together?

 

Ummmm..

 

What are you really wanting to do?

Regular Contributor
Posts: 152

Re: Loop Through Multiple Datasets and Create Field based on Dataset Name?

The need to do this arose from requiring a chart listing and each code of A01 etc. means something different.  So if a chart had two codes then I want the chart to show up twice in the listing, once with HH = A01 and one with HH = A02.

 

I tried as Glen suggested but because the chart is only in the main list once, the second HH would overwrite the first, not add the chart to the group.

 

So I tried to split out  so that there was one chart per HH so I could then dynamically assign HH (i.e. A01 and A02) and then merge back together to have one line per chart where an HH occurred, where a chart that had more than one HH would be in the list twice.  I didn't want to have to create the HH field per the 30 datasets and then merge together due to amount of code thus my query.

 

But if there is an easier way, I am all ears! 

Super User
Super User
Posts: 7,401

Re: Loop Through Multiple Datasets and Create Field based on Dataset Name?

Sorry, your not making any sense.  Start again.  Post example test data, in the form of a datastep.  Then show what the output should look like.  Then clearly state any other requirements.  There is never  need to split data up to set it back together again, you have arrays for transposed data and normalisation and other techniques to deal with these kinds of things.

Regular Contributor
Posts: 152

Re: Loop Through Multiple Datasets and Create Field based on Dataset Name?

data test_grp;

input @1 chartno $5.

               @6 acctno  $5.

               @11 A01            $1.

               @12 A02            $1.

               @13 A03            $1.

               @14 A04    $1.;

 

cards;

11111222220101

22222333331000

33333444440010

44444555551100

55555666660010

run;

 

A01 to A04 is a series of criteria and any that are true I want the true value to become the field of HH and I want one line of data PER true HH.  The example below is what I would like where, for example, chart 11111 had positive A02 and A04 values so there are two obs for the one chart and the positive fields of A02 and A04 have become the HH values.

 

1111122222A02

1111122222A04

2222233333A01

3333344444A03

4444455555A01

4444455555A02

5555566666A03

 

Thanks for your assistance. 

Frequent Contributor
Frequent Contributor
Posts: 89

Re: Loop Through Multiple Datasets and Create Field based on Dataset Name?

But what are you really trying to do?

 

Count things?

 

You want the number of charts with AO1?

 

data test_grp;
input @1 chartno $5.
               @6 acctno  $5.
               @11 A01            $1.
               @12 A02            $1.
               @13 A03            $1.
               @14 A04    $1.;
cards;
11111222220101
22222333331000
33333444440010
44444555551100
55555666660010
run; 

data a01_is_one;
	set test_grp;
	if A01 = 1;
run;

 

Regular Contributor
Posts: 152

Re: Loop Through Multiple Datasets and Create Field based on Dataset Name?

I would like a dataset where each positive data element of A01 to A04 has an observation so I can create a chart list based on each of the qualifier of A01 etc.  If the chart has more than one criteria I want the chart to show up in the list more than once.

 

So I'm not trying to summarize or count, I'm just trying to get a dataset with each of the HH values having one observation. 

 

 

Frequent Contributor
Frequent Contributor
Posts: 89

Re: Loop Through Multiple Datasets and Create Field based on Dataset Name?

I think you are focused on the wrong thing and have the cart before the horse, although I may not fully understand your situation. Apologies if I give offense.

 

You can "create a chart list" of all the A01 charts and all the A02 charts and all the A03 charts.

 

The code I just posted is a list of all the A01 charts, for example. You can do the same thing to create a list of all the A02 charts.

 

 

Regular Contributor
Posts: 152

Re: Loop Through Multiple Datasets and Create Field based on Dataset Name?

Hey HB, thanks but each chart is assigned to a certain group i.e. medicine, surgery etc. so I want to select on that to get all applicable charts and their HH values.  So that is why I want one complete list of all HH values per chart, even if the chart shows up more than once, so I can either run a list on the HH values OR on the group. 

 

With your option I don't have that flexibility.  I actually did what you've done but then wanted to merge all together AND create a field of "HH" which is where this thread started.  

Solution
‎01-12-2017 12:20 PM
Super User
Super User
Posts: 7,401

Re: Loop Through Multiple Datasets and Create Field based on Dataset Name?

Here are two options:

data test_grp;
input @1 chartno $5.
               @6 acctno  $5.
               @11 A01            $1.
               @12 A02            $1.
               @13 A03            $1.
               @14 A04    $1.;
 
cards;
11111222220101
22222333331000
33333444440010
44444555551100
55555666660010
run;

data want (drop=a0:);
  set test_grp;
  array vars{*} a0:;
  do i=1 to dim(vars);
    if vars{i}="1" then do;
      actual=vname(vars{i});
      output;
    end;
  end;
run;

/* Option 2 */
proc transpose data=test_grp out=want2;
  by chartno acctno;
  var a0:;
run;
data want2;
  set want2 (where=(col1="1"));
run;

Regular Contributor
Posts: 152

Re: Loop Through Multiple Datasets and Create Field based on Dataset Name?

Hey RW9, this is great, thanks. 

 

For either of your options, what if the prefix to the field wasn't A i.e. there was A01 to A04, B13, C19 etc.  How would that change the code?  Or would I need to run the same for each iteration and then merge? 

 

Thanks.

Super User
Posts: 5,083

Re: Loop Through Multiple Datasets and Create Field based on Dataset Name?

Take a closer look at Option 1 here.  There are just a couple of items to consider.

 

First, instead of the variable name ACTUAL, you would be looking to use HH:

 

HH = vname(vars{i});

 

Second, your test data shows values of "0" or "1" only.  But your question mentions values greater than 0.  So you may need to change the condition to use:

 

if vars{i} > "0" then do;

 

With those changes, I think that solution would work for what you are asking.

 

It's a little unusual to read in your A0x variables as character instead of numeric.  It can make comparisons trickier.  Here, there is no problem since they are all single digits (unless you have non-numeric values that that need to be treated properly.)

Regular Contributor
Posts: 152

Re: Loop Through Multiple Datasets and Create Field based on Dataset Name?

Thanks Astounding.  Yes  the values are 0 or 1 only because either the condition applies or it doesn't but thanks for the heads up in case other values may be in the dataset for future projects. 

 

So I changed it a bit based on my last post so that array vars{*} includes other values i.e. a0: b1: c1: ; and any others so that whatever the field is it will be captured.  And I changed actual to HH

 

And it works!  Thanks so much to all of you for your persistence.  Much, much less code than I was prepared for which is always good.

 

 

☑ This topic is SOLVED.

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

Discussion stats
  • 13 replies
  • 290 views
  • 0 likes
  • 5 in conversation