proc format cntlin from a raw dataset

[ballardw]

You really need to walk us through what is "tedious".

I don't think you have defined "Provcat" for us anywhere, at least not clearly enough that I can see it.

Your first block of information is basically illegible and I am afraid that I don't understand how any of it is actually used. Since your Outdta.crosswalk_formats is not in the form of data step code it is extremely difficult to see how any of that set is actually used.

If a 1/0 coded value is acceptable you might consider as part of the solution a simple comparison. For example in a data step   b = (a>c) will assign a value of 1 when A is greater than C and 0 otherwise.

A comparison of variables when key values match, as in an SQL join may be possible.

And why create 3 different variables with the exact same comparison??

	if put(catx("_", of plan_type ProvType), $fmt_provtype.) = "VALID" then TYPE_HOSP = 1;
	if put(catx("_", of plan_type ProvType), $fmt_provtype.) = "VALID" then SPEC_HOSP = 1;
	if put(catx("_", of plan_type ProvType), $fmt_provtype.) = "VALID" then TAX_HOSP = 1;

 At least

if put(catx("_", of plan_type ProvType), $fmt_provtype.) = "VALID" then do;  
    TYPE_HOSP = 1;
    SPEC_HOSP = 1;
    TAX_HOSP = 1;
end;

If you want to pull in one or more variables from a lookup table, your ugly "formats" not in the Cntlin data set, you might just be able to use an SQL join. Then just request all the other variables from the look up as needed. The following example doesn't even use the same variable names between the two data sets so you can see how to match on the values as needed. This uses Cat1 and Cat2 in the Lookup data set as key values. Something that matches both values can bring in the Result, or additional variables referenced by the key values. Nothing magic about two variables, just used as an example as that seems to be what you are needing. The "key" could be 3 values such as Country, State/Province, City. If there were more variable needed to from the lookup then they would each be referenced in the Select such as lookup.result2, lookup.result3, lookup.someothervariablename. So if "ProdCat" were in the place of Result ...

data lookup;
   input cat1 $ cat2 $ result $;
datalines;
a b John
a c Jim
a d July
b b Mike
b c Mary
;


data work;
   input var1 $ var2 $ otherdata $;
datalines;
a b Smith
a b Johnson
a d Oreilly
b b Jones
;

proc sql;
   create table want as
   select work.*, lookup.result
   from work
        left join
        lookup
        on work.var1=lookup.cat1
         and work.var2 = lookup.cat2
  ;
quit;

[A_Swoosh]

ProvCAT is the final value that I want to obtain which is currently blank in dataset 2. The ProvCAT value would be derived using a combination of Provider Specialty (ProvSpec1--ProvSpec3) and ProvType.  

The first block of information is a large dataset with:

1. All my possible values found in my data (my reference)

2. The valuetype (the category it applies to--i.e., ProvType variable or ProvSpec variable in the corresponding dataset)

3. The program it applies to (crosswalk type)

4. And, the conditional logic I want to end up applying to derive my ProvCat

Step 1:

I want to take my values (VALUE) and apply that to my actual dataset values for the corresponding variables (Valuetype in Dataset1 which corresponds to ProvType/ProvSpec array in Dataset2). Using this list of values, I want to match them together or assign a format to show they meet.

Step 2:

I want to then create a flag of 1/0 when/if they meet those conditions. So, if the taxonomy value falls in that list, then flag. Same for ProvType, and same for ProvSpec. Go through my list of ProvSpec1-ProvSpec3 and if it contains a value in that set, flag. This is why I was thinking to create a separate flag for each variable type (ProvType,Tax,Spec).

Step 3:

Then I will create a conditional logic using the conditional string portion to create my ProvCAT. So for example,

if Type_Spec=1 or Type_Prov=1 then ProvCAT=Hospital

[ballardw]

I think your Provcat should be added to your big data set then the lookup should be easier.

And if you are intending to place conditional logic in the result of a format then you have a lot of work to do on how to use that.

How about providing a) smaller examples of the two data sets, b) the rules and c) desired result for your example.

You don't need to show every example, just enough to demonstrate what is needed. But data should be some form, like data step code, where the value of a variable can be determined clearly.

Hint: almost anytime you say "array" you likely don't want a format because formats are for single values.

[ChrisNZ]

1. I don't see the string VALID anywhere in the data, so you don't see how you can match this formatted value.

2. This

		start = catx("_", of Crosswalk_Type value) ;
		label = catx("_", of valuetype PROV_CAT_ABBREV);
		output;
		if eof then do;
			start = "OTHER";
			label = "";
			fmtname = "fmt_PROVSPEC";
			output;
		end;
		if eof then do;
			start = "OTHER";
			label = "";
			fmtname = "fmt_PROVTYPE";
			output;
		end;
		if eof then do;
			start = "OTHER";
			label = "";
			fmtname = "fmt_TAX";
			output;
		end;

would be better written as

		start = catx("_", Crosswalk_Type, value) ;
		label = catx("_", valuetype, PROV_CAT_ABBREV);
		output;
		if eof then do;
			start = "OTHER";
			label = "";
			fmtname = "fmt_PROVSPEC";
			output;
			fmtname = "fmt_PROVTYPE";
			output;
			fmtname = "fmt_TAX";
			output;
		end;

3. > iterate through many categories.

Why don't you add the category in START = catx(...    ?

4. Why use a format and not a join ?