Complex logic

[JediApprentice]

Here is some sample data:

data have;
  infile datalines;
  input list $ id price;
  datalines;
DAC 22 2
DAC 11 3
DAC 33 1
BAC 11 4
BAC 33 2
BAC 22 5
CBA 33 5
CBA 22 4
CBA 11 7
;
run;

An ID will pay off one of the lists, for example 22 will be paid off of DAC at a price of 2. What I want to figure out is what the ID would have been priced at if it paid off of a different list. So if it paid off of CBA, the price would be 4, but I need a way to automate this with a program to figure out for all ids and lists.

[ballardw]

"All lists" - what are they? What do they contain?

What would the result for your example data look like? It really isn't clear to me what your desired result would be though it sounds like a possible cartestian product between a data set of the "list" values and another set.

[JediApprentice]

Sorry about that- I was just about to edit because I realized it wasn't clear. Basically, out of that sample input, what I would want to see as output is this:

id DAC BAC CBA
11 3   4   7
22 2   5   4
33 1   2   5

Thanks.

[ballardw · Accepted solution]

For the given example this works:

data have;
  infile datalines;
  input list $ id price;
  datalines;
DAC 22 2
DAC 11 3
DAC 33 1
BAC 11 4
BAC 33 2
BAC 22 5
CBA 33 5
CBA 22 4
CBA 11 7
;
run;

proc sort data=have;
   by id;
run;

proc transpose data=have out=want(drop=_name_);
   by id;
   id list;
   var price;
run;

There would be problems with duplicate values of LIST for any given ID though.

[JediApprentice]

@ballardw Is there a way to also order the list columns alphabetically? I know that in this particular example, I can do this:

proc sort data=have;
  by id list;
run;

(adding list to the sort before the transpose). But this does not work on my real dataset. Why is this? Is it possible to sort columns of lists alphabetically in output dataset?

[ballardw]

If the column's aren't coming out in desired sort order it is likely that is because the first ID does not have all of the List values. The order from the sort would cause the columns to be in order for the first ID value encountered and then a column added as new values of List are encounterd for other ID values.

For example:

data have;
  infile datalines;
  input list $ id price;
  datalines;
DAC 22 2
DAC 11 3
DAC 33 1
BAC 11 4
BAC 33 2
BAC 22 5
CBA 33 5
CBA 22 4
CBA 11 7
AAA 41 3
;
run;

Sorting by ID the List AAA doesn't occur until ID 41 so you get AAA as the LAST column.

You can prepend a dummy data set with a speciall value of ID to get the sort order correct and then drop from the final result.

data have;
  infile datalines;
  input list $ id price;
  datalines;
DAC 22 2
DAC 11 3
DAC 33 1
BAC 11 4
BAC 33 2
BAC 22 5
CBA 33 5
CBA 22 4
CBA 11 7
AAA 41 3
;
run;

data dummy;
   Price=.;
   id = 01; /* should be BEFORE the first sort order ID actually in your data*/
   length list $ 8;
   do LIST = 'DAC','BAC','AAA','CBA';
      output;
   end;
run;
data temp;
   set dummy have;
run;
proc sort data=temp;
   by id list;
run;

proc transpose data=temp out=want(drop=_name_ where=(id ne 1));
   by id;
   id list;
   var price;
run;

[Astounding]

I would go with:

proc format cntlin=have (rename=(list=fmtname id=start price=label));

run;

This creates three formats that you can use in later processing.  For example:

data want;

set have;

cba_price = input( put(id, cba.), 3.);

run;

[s_lassen]

The easiest way may be to index your price reference data on LIST and ID, e.g.

proc sql;
  create unique index key1 on have(list,id);
quit;

You can then use the ID and the price list acronym to find your prices, e.g.:

 75         data want;
 76           set have;
 77           where list='DAC';
 78           do list='BAC','CBA','NON';
 79             set have key=key1/unique;
 80             if _iorc_ then do;
 81               _error_=0;
 82               put 'No price found: ' _ALL_;
 83               end;
 84             else output;
 85             end;
 86         run;
 
 No price found: list=NON id=11 price=7 _ERROR_=0 _IORC_=1230015 _N_=1
 No price found: list=NON id=22 price=4 _ERROR_=0 _IORC_=1230015 _N_=2
 No price found: list=NON id=33 price=5 _ERROR_=0 _IORC_=1230015 _N_=3
 NOTE: There were 3 observations read from the data set WORK.HAVE.
       WHERE list='DAC';
 NOTE: The data set WORK.WANT has 6 observations and 3 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.03 seconds
       cpu time            0.04 seconds

I checked the SET return code, _IORC_, and set error to 0, in order to customize the message when a price for an item was not found in a list (in the example, all the unfindable items came from came from the non-existent list NON) - otherwise we would just get a standard error dump, with no message. Apart from the usage of _IORC_ (you may want to look up the SAS Institute macro %SYSRC and its usage), there is one other important thing to remember about the use of SET with KEY=, namely that no variables are set (apart from _ERROR_ and _IORC_) when the read does not succeed - notice how the PRICE variable in the log message always has the previous value read (from the CBA list).