topic Re: Creating tables with proc sql based on subqueries? in SAS Programming

Creating tables with proc sql based on subqueries?

elwayfan446 — Wed, 13 Jan 2016 15:10:38 GMT

Hi, I am trying to create a new table or data set differently based on logic that looks for specific records in a data set. For example, I want to create a new data set with a WTD, MTD, YTD, and LTD column from the original data set if all of those exist. If they don't (in my case there is no WTD in the original data set) then I want to insert a dummy WTD record and union it to the MTD, YTD, and LTD records from the original data set for the new data set. The way I have written the code, I get the following error:

ERROR: A Composite expression (usually a subquery) is used incorrectly in an expression.

Here is the code I have written. Is there a way to acomplish what I am tring to do?

proc sql;
	create table FLOWFRED.FMFLOW_LOANSFUNDED_20YR_RPT_APP as
		SELECT DISTINCT
			CASE 
				WHEN EXISTS
					(
					  select * 
					  from FLOWFRED.FMFLOW_LOANSFUNDED_20YR_RPT a3
					  where TIMEFRAME = 'WTD'
					)
				THEN 
					(
						select 
					   		b.TIMEFRAME,
							b._TYPE_ as _TYPE_,
							b._PAGE_ as _PAGE_,
							b._TABLE_ as _TABLE_,
							b.INVESTOR_LOAN_ID_N,
							b.NOTE_BALANCE_Sum,
							b.NOTE_BALANCE_Mean,
							b.WGT_AVG_20YR_Mean,
							b.LTV_Mean,
							b.FICO_Mean,
							b.SRP_RATE_Mean,
							b.SRP_AMT_Mean,
							b.SRP_AMT_Sum,
							b.PCT_TOTAL_UPB_PURCHASE_Mean,
							b.PCT_TOTAL_UPB_REFI_Mean,
							b.PCT_TOTAL_CNT_ESCROW_Mean,
							b.PCT_TOTAL_CNT_NONESCROW_Mean,
							CASE
								WHEN b.TIMEFRAME = 'WTD' then 1
								WHEN b.TIMEFRAME = 'MTD' then 2
								WHEN b.TIMEFRAME = 'YTD' then 3
								WHEN b.TIMEFRAME = 'LTD' then 4
							END as TIMEFRAME_NO
						from FLOWFRED.FMFLOW_LOANSFUNDED_20YR_RPT b
						/*order by TIMEFRAME_NO*/
					)
				else 
					(
						SELECT 
							'WTD' as TIMEFRAME,
							'1' as _TYPE_,
							1 as _PAGE_,
							1 as _TABLE_,
							0 as INVESTOR_LOAN_ID_N,
							0 as NOTE_BALANCE_Sum,
							0 as NOTE_BALANCE_Mean,
							0 as WGT_AVG_20YR_Mean,
							0 as LTV_Mean,
							0 as FICO_Mean,
							0 as SRP_RATE_Mean,
							0 as SRP_AMT_Mean,
							0 as SRP_AMT_Sum,
							0 as PCT_TOTAL_UPB_PURCHASE_Mean,
							0 as PCT_TOTAL_UPB_REFI_Mean,
							0 as PCT_TOTAL_CNT_ESCROW_Mean,
							0 as PCT_TOTAL_CNT_NONESCROW_Mean,
							1 as TIMEFRAME_NO
					    from FLOWFRED.FMFLOW_LOANSFUNDED_20YR_RPT a	
					    union 
					    select 
					   		b.TIMEFRAME,
							b._TYPE_ as _TYPE_,
							b._PAGE_ as _PAGE_,
							b._TABLE_ as _TABLE_,
							b.INVESTOR_LOAN_ID_N,
							b.NOTE_BALANCE_Sum,
							b.NOTE_BALANCE_Mean,
							b.WGT_AVG_20YR_Mean,
							b.LTV_Mean,
							b.FICO_Mean,
							b.SRP_RATE_Mean,
							b.SRP_AMT_Mean,
							b.SRP_AMT_Sum,
							b.PCT_TOTAL_UPB_PURCHASE_Mean,
							b.PCT_TOTAL_UPB_REFI_Mean,
							b.PCT_TOTAL_CNT_ESCROW_Mean,
							b.PCT_TOTAL_CNT_NONESCROW_Mean,
							CASE
								WHEN b.TIMEFRAME = 'WTD' then 1
								WHEN b.TIMEFRAME = 'MTD' then 2
								WHEN b.TIMEFRAME = 'YTD' then 3
								WHEN b.TIMEFRAME = 'LTD' then 4
							END as TIMEFRAME_NO
						from FLOWFRED.FMFLOW_LOANSFUNDED_20YR_RPT b
						/*order by TIMEFRAME_NO*/
					) END as x
		FROM FLOWFRED.FMFLOW_LOANSFUNDED_20YR_RPT a;
quit;

Re: Creating tables with proc sql based on subqueries?

RW9 — Wed, 13 Jan 2016 15:23:18 GMT

Hi,

Sorry, thats a bit TL;DR for me. I can tell at a brief glance that that wont work or wont be the best way of doing it. I would recommend posting a small datastep with some test data in, just a few lines, and what you want the output to look like. At a guess on the logic, first create a distinct table of the data you want out, then just left join onto that the data from your main table where the condition is true.

proc sql;
  create table WANT as
  select  distinct ...
  from    HAVE A
  left join (select ... where condition is true) B
  on ...;
quit;

Its vague as I have no test data.

Re: Creating tables with proc sql based on subqueries?

elwayfan446 — Wed, 13 Jan 2016 15:32:24 GMT

Thanks RW9. I was not sure if a left join would work since this would be acting like a merge or union. Maybe if I lay it out like this:

Original Table has the following observations:

MTD field 2, field 3, etc.

YTD field 2, field 3, etc.

LTD field 2, field 3, etc.

In that table, there is no WTD. In the new table I need a WTD so I would create a "dummy" record with zeros for the other fields. I need to check to see if that WTD exists initially to determine if I need a dummy record or not. If it exists, then I simply need to take all data from the original table.

Final table would look like

WTD, field 2, field 3, etc. (whether this is from dummy data or the data in the original table)

MTD field 2, field 3, etc.

YTD field 2, field 3, etc.

LTD field 2, field 3, etc.

Re: Creating tables with proc sql based on subqueries?

Reeza — Wed, 13 Jan 2016 15:41:36 GMT

Missing level problem. Create a table that has all the levels you need. Then you merge them together and if a value doesn't exist the join will add it in, but if all records are present it will work as intended.

Re: Creating tables with proc sql based on subqueries?

RW9 — Wed, 13 Jan 2016 15:43:44 GMT

Data is key here to understanding, post some test data. Here is a an example (verbose coding but I am just leaving for the day). Create a template dataset, then merge back to it:

data have;
  id=1; a="MTD"; field2=1; output;
  id=1; a="YTD"; field2=1; output;
  id=1; a="LTD"; field2=1; output;
  id=2; a="MTD"; field2=1; output;
  id=2; a="YTD"; field2=1; output;
  id=2; a="LTD"; field2=1; output;
  id=2; a="WTD"; field2=1; output;
run;

proc sort data=have out=tmp nodupkey;
  by id;
run;
data tmp;
  set tmp (keep=id);
  do a="MTD","YTD","LTD","WTD";
    output;
  end;
run;

proc sql;
  create table WANT as
  select  A.*,
          B.FIELD2
  from    TMP A
  left join HAVE B
  on      A.ID=B.ID
  and     A.A=B.A;
quit;

Re: Creating tables with proc sql based on subqueries?

elwayfan446 — Wed, 13 Jan 2016 15:46:52 GMT

Reeza, can you point me to an example of this? I am new to this one.

Re: Creating tables with proc sql based on subqueries?

elwayfan446 — Wed, 13 Jan 2016 15:47:13 GMT

Thanks, I will give this a try too.

Re: Creating tables with proc sql based on subqueries?

Astounding — Wed, 13 Jan 2016 15:48:37 GMT

This sounds like a good example of when to use macro language. To prepare, get two working versions of your program. One should work when WTD does exist, and the other should work when WTD doesn't exist.

When those are ready, it is likely a straightforward macro language application. Determine whether WTD exists, and have the macro implement one version of the program or the other.

Once we're that far, we can talk about the best implementation that allows for sets of variables to exist or not. (You won't need dozens of versions of a working program. Macro language will take care of that.)

Re: Creating tables with proc sql based on subqueries?

FreelanceReinh — Wed, 13 Jan 2016 15:51:01 GMT

It looks like you try to use a CASE structure to create a new table conditionally. However, to my knowledge, you can only use it to create a new variable. Also, in the beginning you talk about a WTD column, but later about a WTD record (i.e row) and in your code 'WTD' occurs only as a value of a character variable TIMEFRAME.

In your second post you show a dataset with only three observations. Is this true also for your real data or do you have more records there, maybe with a key variable (not shown so far; see RW9's variable ID) and blocks of 3 or 4 records with TIMEFRAME values 'MTD', 'YTD', 'LTD' and possibly 'WTD'? If this is the case, I think you could achieve what you want even without merging, PROC SQL, auxiliary datasets or macros, in a single data step.

Re: Creating tables with proc sql based on subqueries?

Reeza — Wed, 13 Jan 2016 15:51:48 GMT

@RW9 solution is the method I was suggesting.

Basically, you require a master list. Then merge your data into the master table, and any missing records are added in via the join.

Re: Creating tables with proc sql based on subqueries?

elwayfan446 — Wed, 13 Jan 2016 15:55:45 GMT

Sorry for the confusion about this. Yes, WTD, MTD, YTD, and LTD would be values of the TIMEFRAME variable in the data set. The data set has several variables other than TIMEFRAME for each observation. My goal is to create a dummy observation for WTD if it doesn't exist in the original data set, otherwise, just pull the observations for WTD, MTD, YTD, and LTD that will be in the original data set.

I hope that is a better explanation. Again, sorry for the confusion.

Re: Creating tables with proc sql based on subqueries?

elwayfan446 — Wed, 13 Jan 2016 16:18:43 GMT

I have taken this code and modified it for my data.

data FLOWFRED.FMFLOW_LOANSFUNDED_20YR_RPT;
	id=1; a="MTD"; _TYPE_=1; output;
	id=1; a="YTD"; _TYPE_=1; output;
	id=1; a="LTD"; _TYPE_=1; output;
	id=2; a="WTD"; _TYPE_=1; output;
	id=2; a="MTD"; _TYPE_=1; output;
	id=2; a="YTD"; _TYPE_=1; output;
	id=2; a="LTD"; _TYPE_=1; output;
run ;

proc sort data=flowfred.fmflow_loansfunded_20yr_rpt out=tmp nodupkey;
	by id;
run;

data tmp;
	set tmp (keep=id _TYPE_);
	do a="WTD","MTD","YTD","LTD";
		output;
	end;
run;

proc sql;
	create table FLOWFRED.FMFLOW_LOANSFUNDED_20YR_RPT_APP as
	select A.*,
		b._TYPE_
	from tmp A
	left join flowfred.fmflow_loansfunded_20yr_rpt b on a.id=b.id
		and a.a=b.a;
quit;

Here are the results I am getting from this. Note that currently the original data set currently has an observation that includes WTD in the variable TIMEFRAME. How should I interpret the results I am getting? Is this what would be expected?

id _TYPE_ a

1       1            WTD
1       1      MTD
1       1            YTD
1       1      LTD
2       1           WTD
2       1           MTD
2       1          YTD
2       1          LTD

Re: Creating tables with proc sql based on subqueries?

FreelanceReinh — Wed, 13 Jan 2016 16:18:07 GMT

So, assuming your existing dataset (call it HAVE) has only 3 or 4 observations (depending on whether an observation with TIMEFRAME='WTD' exists), you could create the new dataset (call it WANT) as shown below:

/* Scenario 1: WTD record exists */

data have;
input timeframe $ field2 field3;
cards;
MTD 1 11
YTD 2 22
LTD 3 33
WTD 4 44
;

data want;
set have end=last;
wtdex+(timeframe='WTD');
output;
if last & not wtdex then do;
  call missing(of _all_);
  timeframe='WTD';
  output;
end;
drop wtdex;
run;

/* Scenario 2: WTD record does not exist */

data have;
set have(obs=3);
run;

/* data want; ... same solution as above */

Re: Creating tables with proc sql based on subqueries?

Reeza — Wed, 13 Jan 2016 16:29:09 GMT

Does that solution extend to past WTD? What if LTD is missing instead?

Re: Creating tables with proc sql based on subqueries?

FreelanceReinh — Wed, 13 Jan 2016 17:57:44 GMT

If you do have a key variable such as ID and blocks of 3 or 4 observations (the number possibly varying from one ID to the next), you can still follow the simple one-data-step approach:

data have;
input id timeframe $ field2 field3;
cards;
1 MTD 11 101
1 YTD 21 201
1 LTD 31 301
2 MTD 12 102
2 YTD 22 202
2 LTD 32 302
2 WTD 42 402
;

data want;
set have;
by id;
if first.id then wtdex=0;
wtdex+(timeframe='WTD');
output;
if last.id & not wtdex then do;
  call missing(of _all_);
  timeframe='WTD';
  output;
end;
drop wtdex;
run;

Re: Creating tables with proc sql based on subqueries?

elwayfan446 — Wed, 13 Jan 2016 16:42:23 GMT

Because I am not familiar with the cards; piece of this... I am having a little trouble understanding. Maybe if I add the remaining variables for the data set (have and want) then you can help me translate that a little bit. Here are all the variables...

TIMEFRAME

_TYPE_
_PAGE_
_TABLE_
INVESTOR_LOAN_ID_N
NOTE_BALANCE_Sum
NOTE_BALANCE_Mean
WGT_AVG_20YR_Mean
LTV_Mean
FICO_Mean
SRP_RATE_Mean
SRP_AMT_Mean
SRP_AMT_Sum
PCT_TOTAL_UPB_PURCHASE_Mean
PCT_TOTAL_UPB_REFI_Mean
PCT_TOTAL_CNT_ESCROW_Mean
PCT_TOTAL_CNT_NONESCROW_Mean

Re: Creating tables with proc sql based on subqueries?

FreelanceReinh — Wed, 13 Jan 2016 16:48:49 GMT

Good point, @Reeza. I assumed (based on elwayfan446's posts) that only 'WTD' could be missing.

@elwayfan446: Would the requested code need to fill in missing 'MTD', 'YTD' or 'LTD' records as well? What if all four were missing? How would we know the ID (if there is one)?

I think, the number and names of additional variables are less important. We could name them field2, field3 etc. for the time being without loss of generality.

The only important question is: Do we have an "ID" (and blocks of 3 to 4 or maybe 1 to 4 observations per ID) or are we talking about a HAVE dataset with max. 4 observations?

Re: Creating tables with proc sql based on subqueries?

Reeza — Wed, 13 Jan 2016 16:54:15 GMT

Cards is used to generate sample data to test code. Providing a variable list does not help, providing sample data that mimics your issue is helpful.

You can read here on how to produce a Minimum, Complete and Verifable example.

http://stackoverflow.com/help/mcve

Re: Creating tables with proc sql based on subqueries?

elwayfan446 — Wed, 13 Jan 2016 16:55:35 GMT

@FreelanceReinh While unlikely, I suppose there could be a missing MTD, YTD, and LTD. However, if WTD is not missing then it spills over into the others. That being said, having code to check that in case might not be a bad idea.

I don't have an ID specific to the observations other than the TIMEFRAME variable. The HAVE dataset will only have a max of 4 observations (WTD, MTD, YTD, and LTD)

Re: Creating tables with proc sql based on subqueries?

elwayfan446 — Wed, 13 Jan 2016 17:02:52 GMT

@Reeza Thank you for this reference. This is great to know going forward.