How to correctly use proc format

[Nrjn7]

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.

[Cynthia_sas · Accepted solution]

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

[Nrjn7]

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

[Kurt_Bremser]

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.

[Nrjn7]

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

[ballardw]

---

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

[PaigeMiller]

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.

[andreas_lds]

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

-.

[Ksharp]

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;