topic Re: Loop Through Multiple Datasets and Create Field based on Dataset Name? in SAS Programming

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

shellp55 — Thu, 12 Jan 2017 16:03:58 GMT

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.

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

ballardw — Thu, 12 Jan 2017 16:09:01 GMT

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;

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

HB — Thu, 12 Jan 2017 16:13:54 GMT

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

Ummmm..

What are you really wanting to do?

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

shellp55 — Thu, 12 Jan 2017 16:20:37 GMT

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!

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

RW9 — Thu, 12 Jan 2017 16:26:39 GMT

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.

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

shellp55 — Thu, 12 Jan 2017 16:40:38 GMT

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.

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

HB — Thu, 12 Jan 2017 16:46:37 GMT

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;

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

shellp55 — Thu, 12 Jan 2017 16:51:20 GMT

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.

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

RW9 — Thu, 12 Jan 2017 17:01:48 GMT

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;

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

HB — Thu, 12 Jan 2017 17:06:16 GMT

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.

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

shellp55 — Thu, 12 Jan 2017 17:11:42 GMT

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.

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

Astounding — Thu, 12 Jan 2017 17:14:03 GMT

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.)

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

shellp55 — Thu, 12 Jan 2017 17:15:07 GMT

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.

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

shellp55 — Thu, 12 Jan 2017 17:20:20 GMT

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.