BookmarkSubscribeRSS Feed
vanpeltm1785
Obsidian | Level 7

I will try to explain this the best I can, but I don't even really know what I'm asking for... I have account numbers, and sums of paid amounts split by quarter:

ACCOUNT_IDPaidQuarterInvoice_Amount
123456Y2019 Q11000
123456Y2019 Q31250
123456Y2019 Q4900

 

Notice in this hypothetical, there was no invoice amount for 2019 Q2, so there is no observation... I would like to still have a row for 2019 Q2, with Invoice_amount as summed as 0, even though there aren't any Q2 claims in the dataset

 

Any ideas?

 

edit: I should also mention that the Quarter date is based on the data date range pulled, so I don't want to manually enter the missing date each time

8 REPLIES 8
vanpeltm1785
Obsidian | Level 7

Yes, it is created with this:

 

		,CASE WHEN DATEPART(C.SERVICE_DT) LE &PRIOR. THEN CAT(YEAR(&PRIOR.),' Q',QTR(&PRIOR.),' AND PRIOR') ELSE
		cat(YEAR(DATEPART(C.SERVICE_DT)),' Q',QTR(DATEPART(C.SERVICE_DT))) END AS QUARTER
PeterClemmensen
Tourmaline | Level 20

There are several ways to do this.. Here is one. I added another ACCOUNT_ID for multiple group demonstration.

 

data have;
input ACCOUNT_ID $ 1-6 Paid $ 8 Quarter $ 10-16 Invoice_Amount;
datalines;
123456 Y 2019 Q1 1000
123456 Y 2019 Q3 1250
123456 Y 2019 Q4 900
654321 Y 2019 Q2 1000
654321 Y 2019 Q4 1250
;

data want(drop=rc);
    if _N_=1 then do;
        declare hash h (dataset : 'have');
        h.definekey ('ACCOUNT_ID', 'Quarter');
        h.definedata (all : 'Y');
        h.definedone();
    end;

    set have;
    by account_id;

    if first.account_id then do Quarter = '2019 Q1', '2019 Q2', '2019 Q3', '2019 Q4';
        Invoice_Amount = 0;
        rc = h.find();
        output;
    end;
run;

Result:

 

ACCOUNT_ID Paid Quarter   Invoice_Amount 
123456     Y    2019 Q1   1000 
123456     Y    2019 Q2   0 
123456     Y    2019 Q3   1250 
123456     Y    2019 Q4   900 
654321     Y    2019 Q1   0 
654321     Y    2019 Q2   1000 
654321     Y    2019 Q3   0 
654321     Y    2019 Q4   1250 
Tom
Super User Tom
Super User

This is a classic complete the square problem.

There is an on going discussion of a similar issue on this thread. Just use one of the techniques there.

Re: Help in my pgm logic

yabwon
Onyx | Level 15

Hi,

 

Try this:

data have;
  infile cards dlm="\";
  input ACCOUNT_ID Paid	: $ 1. Quarter : $ 7. Invoice_Amount;
cards;
123456\Y\2019 Q1\1000
123456\Y\2019 Q3\1250
123456\Y\2019 Q4\900
123457\Y\2019 Q1\3000
123457\Y\2019 Q3\3250
;
run;

proc sort data = have;
  by ACCOUNT_ID Quarter;
run;

proc sql;
  create table _tmp_ as
  select distinct ACCOUNT_ID
  from have
  ;
run;

data _tmp_;
  set _tmp_;
  start = input("2018Q04", yyq7.);
  length Quarter $ 7;
  do i = 0 to 5;
    Quarter = tranwrd(put(intnx("quarter", start, i), yyqp6.), ".", " Q");  
    output;
  end;
  keep ACCOUNT_ID Quarter;
run;

data want;
  merge _tmp_  have;
  by ACCOUNT_ID Quarter;
  Invoice_Amount = coalesce(Invoice_Amount,0);
run;

 

All the best

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



vanpeltm1785
Obsidian | Level 7

Hmm, these solutions are all way above my head, to the point where I cannot apply them to my unique situation and get the output

PeterClemmensen
Tourmaline | Level 20

@vanpeltm1785 What makes your actual problem more unique than the problem you posted?

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 570 views
  • 2 likes
  • 5 in conversation