BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shellp55
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

13 REPLIES 13
ballardw
Super User

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;

HB
Barite | Level 11 HB
Barite | Level 11

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

 

Ummmm..

 

What are you really wanting to do?

shellp55
Quartz | Level 8

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! 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

shellp55
Quartz | Level 8

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. 

HB
Barite | Level 11 HB
Barite | Level 11

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;

 

shellp55
Quartz | Level 8

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. 

 

 

HB
Barite | Level 11 HB
Barite | Level 11

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.

 

 

shellp55
Quartz | Level 8

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.  

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

shellp55
Quartz | Level 8

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.

Astounding
PROC Star

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

shellp55
Quartz | Level 8

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.

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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