topic Re: Proc format and do-loop not working in SAS Programming

Proc format and do-loop not working

cash_moonshine — Thu, 06 May 2021 14:29:50 GMT

Hi everyone!

I am having some trouble getting a proc format and data pull to work. First, here is the code for the format I am creating:

data main_code;
  length fmtname $32 type $1 start $8 label $20 hlo $1;
  keep fmtname type start label hlo ;
  type='C';
  hlo='M';
  set lib.code_data ;
  fmtname='source';
  start=code;
  label=source_colors;
  output;
run;
proc sort nodupkey ; by fmtname start label ; run;
proc format cntlin=main_code; run;

The above code is meant to create a format based on a .csv I have already imported into SAS called code_data. The "source_colors" column of the .csv contains four distinct labels: Red, Green, Blue, and Yellow. Each label corresponds to a code in the .csv, and there are instances where a code will appear for more than one label.

The following code is the data pull I am trying to run after this format:

data lib.new_data (keep = red: green: blue: yellow: i10_pr1-i10_pr31);
set lib.old_data;
array proc_cd_{*} i10_pr1-i10_pr31;
red=0;
    green=0;
    blue=0;
    yellow=0;
do i=1 to 31;
            if put(proc_cd_{i}, $source.)= "Red" then do;
                            red=1;
                            red_qual_pr=proc_cd_{i};
                            red_qual_pr_pos=(i);
            end;
end;
do i=1 to 31;
            if put(proc_cd_{i}, $source.)= "Green" then do;
                            green=1;
                            green_qual_pr=proc_cd_{i};
                            green_qual_pr_pos=(i);
            end;
end;
do i=1 to 31;
            if put(proc_cd_{i}, $source.)= "Blue" then do;
                            blue=1;
                            blue_qual_pr=proc_cd_{i};
                            blue_qual_pr_pos=(i);
            end;
end;
do i=1 to 31;
            if put(proc_cd_{i}, $source.)= "Yellow" then do;
                            yellow=1;
                            yellow_qual_pr=proc_cd_{i};
                            yellow_qual_pr_pos=(i);
            end;
end;

The primary problem here is that the do loops do not appear to work. "Red" and "Green" should be showing up in the final dataset with a similar amount of codes, but what ends up happening is that if a code shows up for Red and Green, this data pull is skipping Red and only giving Green a value of 1. I need it to give both Red and Green values of 1 any time that label appears in the $source format.

One workaround I figured out was to create four separate formats for each of red, green, blue, and yellow, but this is inefficient and I was wondering what I can fix to get just the single format above to work for all four labels?

I know this is probably a mess of an explanation, so please feel free to ask questions and I will try to clarify as best as possible.

Thanks for any help!

Re: Proc format and do-loop not working

andreas_lds — Thu, 06 May 2021 14:44:33 GMT

Please post some lines of both datasets as data steps using datalines, so that we have something to work with.

Re: Proc format and do-loop not working

Kurt_Bremser — Thu, 06 May 2021 14:49:56 GMT

Multilabel formats are only good for use in procedures that can deal with them. Using such a format in a PUT function will always result in one value taking precedence.

You may be better off no using a format for your task.

Please post usable examples for code_data and old_data (data steps with datalines, do not skip this!), so we have something for testing and can come up with suggestions.

Re: Proc format and do-loop not working

ballardw — Thu, 06 May 2021 14:51:34 GMT

Without your data sets, both to create the format and to test the values we really can't say what is happening.

A very likely issue may be that you are testing for text "red" but the formatted value is " red" (or similar leading spaces) or "Red" case.

One thing to test when creating formats with Cntlin in to create an output set from Proc Format with Cntlout to see what the result could be.

Test your data step code with something like to see what the actual formatted values are:

data lib.new_data (keep = red: green: blue: yellow: i10_pr1-i10_pr31);
set lib.old_data;
array proc_cd_{*} i10_pr1-i10_pr31;
red=0;
    green=0;
    blue=0;
    yellow=0;
do i=1 to 31;
            put proc_cd= $20. +1 "Formatted: "  proc_cd_{i}= $source.;
end;
run;

One thing to pay attention to is that HLO=M for multilabel formats will only use the multi bit in Proc Summary/Means, Tabulate and Report. This means that only the "top" level formatted value is used for things like "put" in the data step.

Re: Proc format and do-loop not working

cash_moonshine — Thu, 06 May 2021 15:09:46 GMT

Apologies for the missing data! I've tried to recreate what they generally import as here:

data code_data;
   input code $ source_colors $;
   datalines;
0DBC0ZZ Red
0DBE0ZZ Red
0DBC0ZZ Green
0DBE0ZZ Green
0DBC0ZZ Blue
0DBE0ZZ Blue
0DBC0ZZ Yellow
0DBE0ZZ Yellow
;

data old_data;
   input I10_PR1 $ ;
   datalines;
0DBC0ZZ
0DBE0ZZ
0DBC0ZZ
0DBE0ZZ
0DBC0ZZ
0DBE0ZZ
0DBC0ZZ
0DBE0ZZ
;

Hopefully this works. Thanks for the advice so far, let me know if I need to add more to these.

Re: Proc format and do-loop not working

Reeza — Thu, 06 May 2021 15:18:35 GMT

This will create 4 flags, each with two supporting values out of your 31 variables. It will indicate if you have the 4 colours, Red, Green Blue, Yellow. If you have multiple occurrences, you will take only the last values.

Is this what you want to do?

data lib.new_data (keep = red: green: blue: yellow: i10_pr1-i10_pr31);
set lib.old_data;
array proc_cd_{*} i10_pr1-i10_pr31;
red=0;
    green=0;
    blue=0;
    yellow=0;

do i=1 to 31;
            if put(proc_cd_{i}, $source.)= "Red" then do;
                            red=1;
                            red_qual_pr=proc_cd_{i};
                            red_qual_pr_pos=(i);
            end;

            if put(proc_cd_{i}, $source.)= "Green" then do;
                            green=1;
                            green_qual_pr=proc_cd_{i};
                            green_qual_pr_pos=(i);
            end;


            if put(proc_cd_{i}, $source.)= "Blue" then do;
                            blue=1;
                            blue_qual_pr=proc_cd_{i};
                            blue_qual_pr_pos=(i);
            end;


            if put(proc_cd_{i}, $source.)= "Yellow" then do;
                            yellow=1;
                            yellow_qual_pr=proc_cd_{i};
                            yellow_qual_pr_pos=(i);
            end;
end;

run;

@cash_moonshine wrote:

Hi everyone!
I am having some trouble getting a proc format and data pull to work. First, here is the code for the format I am creating:
data main_code;
  length fmtname $32 type $1 start $8 label $20 hlo $1;
  keep fmtname type start label hlo ;
  type='C';
  hlo='M';
  set lib.code_data ;
  fmtname='source';
  start=code;
  label=source_colors;
  output;
run;
proc sort nodupkey ; by fmtname start label ; run;
proc format cntlin=main_code; run;
The above code is meant to create a format based on a .csv I have already imported into SAS called code_data. The "source_colors" column of the .csv contains four distinct labels: Red, Green, Blue, and Yellow. Each label corresponds to a code in the .csv, and there are instances where a code will appear for more than one label.

The following code is the data pull I am trying to run after this format:
data lib.new_data (keep = red: green: blue: yellow: i10_pr1-i10_pr31);
set lib.old_data;
array proc_cd_{*} i10_pr1-i10_pr31;
red=0;
    green=0;
    blue=0;
    yellow=0;
do i=1 to 31;
            if put(proc_cd_{i}, $source.)= "Red" then do;
                            red=1;
                            red_qual_pr=proc_cd_{i};
                            red_qual_pr_pos=(i);
            end;
end;
do i=1 to 31;
            if put(proc_cd_{i}, $source.)= "Green" then do;
                            green=1;
                            green_qual_pr=proc_cd_{i};
                            green_qual_pr_pos=(i);
            end;
end;
do i=1 to 31;
            if put(proc_cd_{i}, $source.)= "Blue" then do;
                            blue=1;
                            blue_qual_pr=proc_cd_{i};
                            blue_qual_pr_pos=(i);
            end;
end;
do i=1 to 31;
            if put(proc_cd_{i}, $source.)= "Yellow" then do;
                            yellow=1;
                            yellow_qual_pr=proc_cd_{i};
                            yellow_qual_pr_pos=(i);
            end;
end;
The primary problem here is that the do loops do not appear to work. "Red" and "Green" should be showing up in the final dataset with a similar amount of codes, but what ends up happening is that if a code shows up for Red and Green, this data pull is skipping Red and only giving Green a value of 1. I need it to give both Red and Green values of 1 any time that label appears in the $source format.

One workaround I figured out was to create four separate formats for each of red, green, blue, and yellow, but this is inefficient and I was wondering what I can fix to get just the single format above to work for all four labels?

I know this is probably a mess of an explanation, so please feel free to ask questions and I will try to clarify as best as possible.

Thanks for any help!

Re: Proc format and do-loop not working

cash_moonshine — Thu, 06 May 2021 15:25:05 GMT

Not quite. Let me see if I can explain better. I am new to SAS so this has proven confusing in more ways than one!

Basically, my hope was for the data step to iterate through old_data 4 times, once each for Red, Green, Blue, and Yellow. Each time a code appeared with an associated label, I wanted it to give a value of 1 to the new binary variables I created. I don't want these to be mutually exclusive, I want the code to do the same thing for each color. But I was wondering how to do this without the need for four separate formats for each color, just the one format that I provided.

Re: Proc format and do-loop not working

cash_moonshine — Thu, 06 May 2021 15:26:53 GMT

Thanks for your reply!

It doesn't look like I have leading spaces or case sensitivity issues here.

Sorry, I am new to SAS so I don't quite understand what you mean regarding HLO.

Re: Proc format and do-loop not working

Reeza — Thu, 06 May 2021 15:36:08 GMT

I think your logic and approach is unfortunately in the wrong direction. Please show what you're expecting as output given your sample input. You have 31 one variables how do you want them consolidated into the 4 flags? How do you want the multiples to be handled, eg. in 31 values you have 4 blue, 8 red, 10 yellow and 9 green? Which record do you want to keep. I'm suspecting you may just need a transpose and a format.

Please expand your example data (old_data) to have at least two or three variables so it's more reflective of your actual data.

Re: Proc format and do-loop not working

cash_moonshine — Thu, 06 May 2021 15:39:50 GMT

I would like my output to look something like this:

I10_PR1 Red Green Blue Yellow

0DBC0ZZ 1 1 1 1

0DBE0ZZ 1 1 1 1
etc.

Instead, what I am getting is this:

I10_PR1 Red Green Blue Yellow

0DBC0ZZ 0 1 1 1

0DBE0ZZ 0 1 1 1

Even though the code is present for Red, it is not being assigned a value of 1 as requested in the do loop.

Re: Proc format and do-loop not working

cash_moonshine — Thu, 06 May 2021 15:49:49 GMT

Sorry, didn't see your edited message. I've tried to expand the data:

data old_data;
   input I10_PR1 $ I10_PR2 $ I10_PR3 $;
   datalines;
0DBC0ZZ 0BQS0ZZ 0BQR4ZZ
0DBE0ZZ 07BP4ZZ 0BQT0ZZ
0DBC0ZZ 0BQS0ZZ 0BQR4ZZ
0DBE0ZZ 07BP4ZZ 0BQT0ZZ
0DBC0ZZ 0BQS0ZZ 0BQR4ZZ
0DBE0ZZ 07BP4ZZ 0BQT0ZZ
0DBC0ZZ 0BQS0ZZ 0BQR4ZZ
0DBE0ZZ 07BP4ZZ 0BQT0ZZ
;

Re: Proc format and do-loop not working

Kurt_Bremser — Thu, 06 May 2021 15:54:30 GMT

See this example:

data code_data;
   input code $ source_colors $;
   datalines;
0DBC0ZZ Red
0DBE0ZZ Red
0DBC0ZZ Green
0DBE0ZZ Green
0DBC0ZZ Blue
0DBE0ZZ Blue
0DBC0ZZ Yellow
0DBE0ZZ Yellow
;

data old_data;
   input I10_PR1 $ ;
   datalines;
0DBC0ZZ
0DBE0ZZ
0DBC0ZZ
0DBE0ZZ
0DBC0ZZ
0DBE0ZZ
0DBC0ZZ
0DBE0ZZ
;

proc sql;
create table long as
  select distinct
    a.I10_PR1,
    b.source_colors,
    1 as value
  from old_data a left join code_data b
  on a.I10_PR1 = b.code
  order by a.I10_PR1
;
quit;

proc transpose data=long out=wide;
by I10_PR1;
id source_colors;
var value;
run;

Now, your wider dataset:

data old_data;
   input I10_PR1 $ I10_PR2 $ I10_PR3 $;
   datalines;
0DBC0ZZ 0BQS0ZZ 0BQR4ZZ
0DBE0ZZ 07BP4ZZ 0BQT0ZZ
0DBC0ZZ 0BQS0ZZ 0BQR4ZZ
0DBE0ZZ 07BP4ZZ 0BQT0ZZ
0DBC0ZZ 0BQS0ZZ 0BQR4ZZ
0DBE0ZZ 07BP4ZZ 0BQT0ZZ
0DBC0ZZ 0BQS0ZZ 0BQR4ZZ
0DBE0ZZ 07BP4ZZ 0BQT0ZZ
;

would first need to be transposed to long, so you have a single column with codes.

Re: Proc format and do-loop not working

Reeza — Thu, 06 May 2021 15:57:07 GMT

You indicated you have 31 variables. How would it show for more than two variables, how will you scale it? If this is the given input, please show the EXACT output. For a single PR code, how are you getting multiple flags?

data code_data;
   input code $ source_colors $;
   datalines;
0DBC0ZZ Red
0DBE0ZZ Red
0DBC0ZZ Green
0DBE0ZZ Green
0DBC0ZZ Blue
0DBE0ZZ Blue
0DBC0ZZ Yellow
0DBE0ZZ Yellow
;
data old_data;
   input I10_PR1 $  I10_PR2 $;
   datalines;
0DBC0ZZ 0DBC0ZZ
0DBE0ZZ 0DBC0ZZ
0DBE0ZZ 0DBC0ZZ
0DBE0ZZ 0DBC0ZZ
0DBE0ZZ 0DBE0ZZ
0DBC0ZZ 0DBE0ZZ
0DBC0ZZ 0DBE0ZZ
0DBC0ZZ 0DBE0ZZ
;

@cash_moonshine wrote:

I would like my output to look something like this:

I10_PR1             Red                Green              Blue                  Yellow

0DBC0ZZ              1                     1                      1                        1

0DBE0ZZ              1                     1                      1                        1
etc.

Instead, what I am getting is this:

I10_PR1             Red                Green              Blue                  Yellow

0DBC0ZZ              0                    1                      1                        1

0DBE0ZZ              0                     1                      1                        1

Even though the code is present for Red, it is not being assigned a value of 1 as requested in the do loop.

Re: Proc format and do-loop not working

ballardw — Thu, 06 May 2021 16:02:01 GMT

@cash_moonshine wrote:

Apologies for the missing data! I've tried to recreate what they generally import as here:
data code_data;
   input code $ source_colors $;
   datalines;
0DBC0ZZ Red
0DBE0ZZ Red
0DBC0ZZ Green
0DBE0ZZ Green
0DBC0ZZ Blue
0DBE0ZZ Blue
0DBC0ZZ Yellow
0DBE0ZZ Yellow
;
data old_data;
   input I10_PR1 $ ;
   datalines;
0DBC0ZZ
0DBE0ZZ
0DBC0ZZ
0DBE0ZZ
0DBC0ZZ
0DBE0ZZ
0DBC0ZZ
0DBE0ZZ
;
Hopefully this works. Thanks for the advice so far, let me know if I need to add more to these.

Your Code_data has values "Red" "Yellow" and "Blue" and "Green" and those will become the formatted values. Your original data step was testing for a formatted value of "red" "yellow" "blue" and "green". So your "IF" comparisons were using the incorrect comparison. They need to use "Red" "Yellow" etc.

It does help to provide what the expected output would be for your data.

Re: Proc format and do-loop not working

cash_moonshine — Thu, 06 May 2021 21:27:38 GMT

...

Re: Proc format and do-loop not working

ballardw — Thu, 06 May 2021 17:07:39 GMT

When code seems not to behave you need to show the LOG for the data step or procedure. All of it, the code with all of the notes, warnings or messages. Copy the text from the Log and paste into a text box opened on the forum with the </> above the message window.

The text box can be extremely critical depending on the text in the log. The main message windows will reformat text which will make some of the diagnostics SAS provides out of order.

Re: Proc format and do-loop not working

Reeza — Thu, 06 May 2021 17:10:10 GMT

So far we've seen code that doesn't work and data that doesn't give you what you want. I'm still waiting to see what you want from more than two variables.

Re: Proc format and do-loop not working

cash_moonshine — Thu, 06 May 2021 17:28:14 GMT

1  data lib.new_data (keep = red: green: blue: yellow:
2! qual_pr qual_pr_pos i10_pr1-i10_pr31
3      set lib.old_data
4     array proc_cd_{*} i10_pr1-i10_pr31;
5
6      red=0;
7      green=0;
8     blue=0;
9      yellow=0;
10
11  do i=1 to 31;
12             if put(proc_cd_{i}, $source.)= "Red" then do;
13                              red=1;
14                              red_qual_pr=proc_cd_{i};
15                              red_qual_pr_pos=(i);
16              end;
17  end;
18  do i=1 to 31;
19              if put(proc_cd_{i}, $source.)= "Green" then do;
20                              green=1;
21                              green_qual_pr=proc_cd_{i};
22                              green_qual_pr_pos=(i);
23              end;
24  end;
25  do i=1 to 31;
26              if put(proc_cd_{i}, $source.)= "Blue" then do;
27                              blue=1;
28                              blue_qual_pr=proc_cd_{i};
29                              blue_qual_pr_pos=(i);
30              end;
31  end;
32  do i=1 to 31;
33             if put(proc_cd_{i}, $source.)= "Yellow" then do;
34                              Yellow=1;
35                              yellow_qual_pr=proc_cd_{i};
36                              yellow_qual_pr_pos=(i);
37              end;
38  end;
39      output;
40
41  run;

WARNING: The variable qual_pr in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable qual_pr_pos in the DROP, KEEP, or RENAME list has never been referenced.

This is what I get.

Re: Proc format and do-loop not working

cash_moonshine — Thu, 06 May 2021 21:29:34 GMT

Essentially, each row is a patient who could have any number of the 31 I10_PR codes. I have imported a data set and created a format from it; this data set is a list of ICD10 codes and a label: Red, Blue, Green, or Yellow. What I want is for the do loop to go through the second, patient data set and if any PR code is present for that patient, give a value of 1 to Red, Green, Blue, or Yellow depending on if that code is associated with any of those color labels.

So if there is a PR code present while the do loop for Red is running, it should give a value of 1 to red when it sees the code and the $source_color label from the format that I created.

Instead, it skips Red, leaves it as 0, and give Green a value of 1 when the Green do loop is running.

I need it to give values of 1 to each color label any time a code is present for one of those labels.

Ultimately, I need to know which color label is contributing the most codes to the final patient data set.

Re: Proc format and do-loop not working

Kurt_Bremser — Thu, 06 May 2021 18:07:04 GMT

So your initial problem is bad dataset structure. With a long dataset, the code is fairly simple:

data code_data;
input code $ source_colors $;
datalines;
0DBC0ZZ Red
0DBE0ZZ Red
0DBC0ZZ Green
0DBE0ZZ Green
0DBC0ZZ Blue
0DBE0ZZ Blue
0DBC0ZZ Yellow
0DBE0ZZ Yellow
;

data old_data;
input patid $ I10_PR1 $ I10_PR2 $ I10_PR3 $;
datalines;
A 0DBC0ZZ 0BQS0ZZ 0BQR4ZZ
B 0DBE0ZZ 07BP4ZZ 0BQT0ZZ
C 0DBC0ZZ 0BQS0ZZ 0BQR4ZZ
D 0DBE0ZZ 07BP4ZZ 0BQT0ZZ
E 0DBC0ZZ 0BQS0ZZ 0BQR4ZZ
F 0DBE0ZZ 07BP4ZZ 0BQT0ZZ
G 0DBC0ZZ 0BQS0ZZ 0BQR4ZZ
H 0DBE0ZZ 07BP4ZZ 0BQT0ZZ
;

proc transpose
  data=old_data
  out=long (drop=_name_ rename=(col1=code))
;
by patid;
var I10:;
run;

proc sql;
create table want_long as
  select distinct
    l.patid,
    c.source_colors,
    1 as value
  from long l inner join code_data c
  on l.code = c.code
;
quit;

proc transpose
  data=want_long
  out=want (drop=_name_)
;
by patid;
id source_colors;
var value;
run;