topic Re: How to correctly use proc format in SAS Programming

How to correctly use proc format

Nrjn7 — Tue, 28 Sep 2021 15:47:34 GMT

I was able to create the following where the inclusion and exclusion might have multiple criteria separated by comma.

ID	exclusion_failed	inclusion_failed
1	exc_1	inc_3
2	exc_1, exc_2	inc_1
3		inc_1, inc_2, inc_3
4	exc_2, exc_3	inc_2, inc_3

How do I properly apply format so the table then looks like this?

ID	exclusion_failed	inclusion_failed
1	1	3
2	1, 2	1
3		1,2,3
4	2, 3	2,3

the code I used to format:

proc format;

value $exclusion 'exc_1' = '1'

'exc_2' = '2'

'exc_3' = '3';

value $inclusion 'inc_1' = '1'

'inc_2' = '2'

'inc_3' = '3';

run;

When I apply the format, it only works for single criteria but for the ones separated by comma doesn't seem to be working, any suggestions.

Re: How to correctly use proc format

Cynthia_sas — Tue, 28 Sep 2021 16:35:48 GMT

Hi: How are you getting or creating the variables called exclusion_failed and inclusion_failed? I'm not clear how or where you are applying the format. If you already have character variable that contains a string of comma separated values, I would not expect your format to work as you envision. If the format works at all, it should only format part of the variable value where the variable contains commas.
Off hand, it might be easier to just use the tranwrd and compbl functions to just get rid of the exc_ and the inc_ pieces of the string.

Cynthia

Re: How to correctly use proc format

Nrjn7 — Tue, 28 Sep 2021 16:52:17 GMT

@Cynthia_sas thank you for your response. I followed the following suggestion to create the respective variables.

https://communities.sas.com/t5/SAS-Programming/Multiple-data-under-a-variable/m-p/746480

Thanks.

Re: How to correctly use proc format

ballardw — Tue, 28 Sep 2021 16:59:25 GMT

@Nrjn7 wrote:

I was able to create the following where the inclusion and exclusion might have multiple criteria separated by comma.

ID exclusion_failed inclusion_failed

1 exc_1 inc_3

2 exc_1, exc_2 inc_1

3 inc_1, inc_2, inc_3

4 exc_2, exc_3 inc_2, inc_3

How do I properly apply format so the table then looks like this?

ID exclusion_failed inclusion_failed

1 1 3

2 1, 2 1

3 1,2,3

4 2, 3 2,3

the code I used to format:

proc format;

value $exclusion 'exc_1' = '1'

  'exc_2' = '2'

  'exc_3' = '3';

value $inclusion 'inc_1' = '1'

  'inc_2' = '2'

  'inc_3' = '3';

run;

When I apply the format, it only works for single criteria but for the ones separated by comma doesn't seem to be working, any suggestions.

To apply a format the values must match the format definition. So if you have "multiple" values like "inc_1, inc_2, inc_3" then you have to have a definition in the format for "inc_1, inc_2, inc_3" = "1,2,3".

Which is just one of the reasons why multiple values in a single variable are generally looked down upon. Because if your values are "inc_1, inc_3, inc_2" that would require a separate entry in the format.

You may be able to do the conversion from the single values as you create the multiple version. Maybe. Provide data from before the multiple values are created.

Re: How to correctly use proc format

PaigeMiller — Tue, 28 Sep 2021 17:44:56 GMT

Does it have to be formats that you use?

How about this:

data want;
    set have;
    exclusion_failed=tranwrd(exclusion_failed,'exc_','');
    inclusion_failed=tranwrd(inclusion_failed,'inc_','');
run;

May I ask for the reason to do this? What can you do with '1,2,3' that you can't do with 'inc_1, inc_2, inc_3'? And once you have '1,2,3', what is next? There may be much easier ways to accomplish the next step, or accomplish the end goal, if only we knew what you were trying to do.

Re: How to correctly use proc format

andreas_lds — Tue, 28 Sep 2021 18:03:54 GMT

Removing the unwanted text as @PaigeMiller already posted seems the easiest way to solve the problem. If you have to use formats, either define all combinations as @ballardw said or use a loop to process each value of the strings (using the functions countw, scan and put).

Re: How to correctly use proc format

Ksharp — Wed, 29 Sep 2021 13:37:21 GMT

You want this ?

data have;
infile cards expandtabs truncover;
input ID	exclusion_failed & $40. inclusion_failed & $40.;
cards;
1	exc_1	inc_3
2	exc_1, exc_2	inc_1
3	 .	inc_1, inc_2, inc_3
4	exc_2, exc_3	inc_2, inc_3
;

proc format;

value $exclusion 'exc_1' = '1'
                          'exc_2' = '2'

                          'exc_3' = '3';

value $inclusion 'inc_1' = '1'
                          'inc_2' = '2'

                          'inc_3' = '3';

run;
data want;
 set have;
 length new_exc new_inc $ 200;
 do i=1 to countw(exclusion_failed,', ');
   new_exc=catx(',',new_exc,put(scan(exclusion_failed,i,', '),$exclusion.));
 end;

 do i=1 to countw(inclusion_failed,', ');
   new_inc=catx(',',new_inc,put(scan(inclusion_failed,i,', '),$inclusion.));
 end;
drop i;
 run;

Re: How to correctly use proc format

Kurt_Bremser — Wed, 29 Sep 2021 13:57:39 GMT

Wide datasets or datasets with concatenated values are ALWAYS harder to work with. Your formats would work if you hadn't made your dataset mostly useless.

You can always "widen" data for reporting purposes, but that is the last step in your process.

Re: How to correctly use proc format

Nrjn7 — Thu, 30 Sep 2021 18:42:03 GMT

@Kurt_Bremser Thanks for the feedback. This is required for creating reports as a final step.