topic Re: Automate code with character variable taking varying values in data step in SAS Programming

Automate code with character variable taking varying values in data step

Cruise — Mon, 29 Apr 2019 13:02:06 GMT

Hello Folks,

I'm trying to subset a large data to fourty smaller datasets based on the "location" AND "code" variables.

Codes are taking different values in datasteps depending on whether sites are A or B or C.

I'd like to reduce human error by automating this subsetting process.

Is there any way? I'm familiar to macro/mend process. But I don't know how when character variable is involved taking different values.

DATA HAVE; 
INPUT ID CODE $ LOCATION $;
CARDS;
1	154	A
1	157	A
1	C25	A
1	153	A
2	183	B
2	185	B
2	196	B
2	196	B
3       200     C
3       100     C 
;

DATA SUBSET_A(COMPRESS=YES);
SET HAVE(WHERE=(LOCATION="A"));
IF CODE IN ('153','154') THEN SITE=1; ELSE SITE=0;
IF CODE IN ('153','154') THEN WEIGHT=1; ELSE
IF CODE IN ('157') THEN WEIGHT=2; ELSE WEIGHT=99;
RUN;

DATA SUBSET_B(COMPRESS=YES);
SET HAVE(WHERE=(LOCATION="B"));
IF CODE IN ('183','185') THEN SITE=1; ELSE SITE=0;
IF CODE IN ('183','185') THEN WEIGHT=1; ELSE
IF CODE IN ('196') THEN WEIGHT=2; ELSE WEIGHT=99;
RUN;

DATA SUBSET_C(COMPRESS=YES);
SET HAVE(WHERE=(LOCATION="C"));
IF CODE IN ('200') THEN SITE=1; ELSE SITE=0;
IF CODE IN ('200') THEN WEIGHT=1; ELSE
IF CODE IN ('100') THEN WEIGHT=2; ELSE WEIGHT=99;
RUN;

Re: Automate code with character variable taking varying values in data step

novinosrin — Mon, 29 Apr 2019 13:10:51 GMT

splitting into many data sets based on LOCATION is pretty straight forward however in your case I wonder how would you automate the condition of the CODE values that constitutes to SITE variables as each of seems to be mutually exclusive

Re: Automate code with character variable taking varying values in data step

Cruise — Mon, 29 Apr 2019 13:29:34 GMT

That's the problem. I wonder if it's possible to define a specific set of 'codes' for the specific combinations of 'Location-site-weight'? as a dictionary to refer? and SAS subsets data with the reference of info in that dictionary? something like that?

Re: Automate code with character variable taking varying values in data step

novinosrin — Mon, 29 Apr 2019 13:32:42 GMT

May be you can define user defined formats using proc format which would be a one time effort to type carefully. In any case, I really do think that's gonna take some boring effort at least once.

Re: Automate code with character variable taking varying values in data step

Astounding — Mon, 29 Apr 2019 13:45:06 GMT

It's certainly possible to set up the known translations as a data set. For example:

data master_list;
input location $ code $ site weight;
datalines;
A 153 1 1
A 154 1 1
A 157 0 2
B 183 1 1
B 185 1 1
B 196 0 2
C 200 1 1
C 100 0 2
;

Then the approach would be to use this as a hash table, where the key is the combination of CODE and LOCATION, and the data is the SITE and WEIGHT.

However, note that the logic is not just a matter of look-up. You have to check whether the table actually contains the combination you are searching for. If it does, great you can use the values found via look-up. But if it doesn't, you still need to set SITE to 0 and WEIGHT to 99.

This is something that @novinosrin can do in his sleep, so I will abandon the coding efforts.