topic Re: SAS doesn't return expected table in SAS Programming

SAS doesn't return expected table

HG66 — Mon, 14 Aug 2023 17:04:10 GMT

%LET StartDate = '01Jul2021'd;

%Let EndDate = '01Jun2023'd;

%macro Members(start,end);

%let dif=%sysfunc(intck(month,&start,&end));

%do i=0 %to &dif;

%let date=%sysfunc(intnx(month,&start,&i,e));

proc sql;

create table Contacts as

select

put(&date., monyy7.) as Date,

count(Id) as Members

from Contact

;

quit;

%end;

%mend Members;

%Members(&StartDate., &EndDate.)

I expect SAS to return 23 rows for 23 months from Jul2021 to Jun2023. However, it returned only 1 row of Jun2023 and I'm not sure why.

Re: SAS doesn't return expected table

Tom — Mon, 14 Aug 2023 17:08:39 GMT

You told it to create a table named CONTACTS.

If you do that 23 times only the last version of it will exist since the previous versions will have been replaced.

But since the table you are generating does not vary based on the date why both making the other 22 anyway?

What are you really trying to do?

Re: SAS doesn't return expected table

HG66 — Mon, 14 Aug 2023 17:12:54 GMT

Thanks for your input. I'm trying to create a table

Date | Members

--------------------

Jul2021 | x

Aug2021 | y

Sep2021 | z

Jun2023 | w

Re: SAS doesn't return expected table

HG66 — Mon, 14 Aug 2023 17:55:06 GMT

i just realized I missed a where condition that makes output dependent on the date

%LET StartDate = '01Jul2021'd;
%Let EndDate = '01Jun2023'd;

%macro Members(start,end);
%let dif=%sysfunc(intck(month,&start,&end));
%do i=0 %to &dif;
%let date=%sysfunc(intnx(month,&start,&i,e));

proc sql;
create table Contacts as
select
put(&date., monyy7.) as Date,
count(Id) as Members
from Contact
where CreatedDate <=&Date.
;
quit;

%end;
%mend Members;

%Members(&StartDate., &EndDate.)

Re: SAS doesn't return expected table

PaigeMiller — Mon, 14 Aug 2023 19:50:15 GMT

First, you have not explained what calculations you are trying to do. So explain what you are trying to do, without talking about SAS code, in words. In your table above, you don't say how to compute w x y and z. Are they simply counts? You haven't stated that, and we shouldn't have to guess.

Macros are not a good choice to replace BY group processing, which seems like what you are trying to do (again, I'm guessing) and PROC SQL is not a good choice in loops to replace PROC FREQ.

It seems as if you are counting the number of ID values, PROC FREQ would be the way to go. But it's really not clear what else you are doing here, as we don't have data set CONTACT and you are not counting BY month in any way in your code. Maybe something like this (but this is a guess until you provide a portion of your data set named CONTACT):

%let startdate = '01jul2021'd;
%let enddate = '01jun2023'd;
proc freq data=contact(where=(&startdate<=date<=&enddate));
    tables date;
    format date monyy7.;
run;

So, no macros needed, and no looping needed, and no SQL needed either.

Re: SAS doesn't return expected table

Tom — Mon, 14 Aug 2023 18:00:44 GMT

So make the dataset once and then INSERT the new observations.

%macro Members(start,end);
%local i date ;

data Contacts;
  length DATE $7 Members 8;
  stop;
run;

proc sql noprint;
%do i=0 %to %sysfunc(intck(month,&start,&end));
  %let date=%sysfunc(intnx(month,&start,&i,e));
insert into Contacts(Date,Members)
select put(&date., monyy7.) as Date
     , count(Id) as Members
from Contact
where CreatedDate <=&Date.
;
%end;
quit;

%mend Members;


%LET StartDate = '01Jul2021'd;
%Let EndDate = '01Jun2023'd;
%Members(&StartDate., &EndDate.)

Re: SAS doesn't return expected table

HG66 — Mon, 14 Aug 2023 18:21:07 GMT

That's a good idea. I tried that but SAS threw an error after inserting the first row of Jul2021:

"ERROR 180-322: Statement is not valid or it is used out of proper order."

Do you know what caused the error?

Re: SAS doesn't return expected table

PaigeMiller — Mon, 14 Aug 2023 18:28:44 GMT

You can't just show us error messages, and not the code that caused the error.

Please show us the LOG for this step (every line in the log for this step, not partial logs for this step). Please copy the log as text and then paste it into the window that appears when you click on the </> icon.

Also please show us a portion of the data in data set CONTACT.

Re: SAS doesn't return expected table

HG66 — Mon, 14 Aug 2023 20:42:21 GMT

Sorry my first day here so I'm still learning things. Here is the code I ran using what Tom suggested [I'll post the log shortly. I can't seem to include it here]

%LET StartDate = '01Jul2021'd;
%Let EndDate = '01Jun2023'd;

%macro Members(start,end);
%local i date ;

data Contacts;
  length DATE $7 Members 8;
  stop;
run;

proc sql noprint;
%do i=0 %to %sysfunc(intck(month,&start,&end));
  %let date=%sysfunc(intnx(month,&start,&i,e));
	insert into Contacts(Date,Members) 
	select 
		 put(&date., monyy7.) as Date,
		 count(Id) as Memebers
	from Contact where CreatedDate<=&date.
	;
	quit;

%end;
%mend Members;

%Members(&StartDate., &EndDate.)

Re: SAS doesn't return expected table

Quentin — Mon, 14 Aug 2023 20:55:46 GMT

You moved the QUIT statement before the %END. It needs to be after the %END, as in Tom's post. As you have it, the QUIT executes after the first INSERT statement, and ends the PROC SQL step. Then the second INSERT statement is an error, because it's not part of a PROC SQL step.

You also have a typo

count(Id) as Memebers  /*should be members*/

But I don't think the typo matters.

Re: SAS doesn't return expected table

HG66 — Tue, 15 Aug 2023 04:04:40 GMT

This is great and I was able to run it. However, I'm curious to know if there's a way I don't have to create a table and insert each row to it?

Re: SAS doesn't return expected table

Tom — Tue, 15 Aug 2023 12:03:55 GMT

Since it looks like you are just counting OBSERVATIONS (you use ID in the COUNT() function, but I doubt you have any observations that have a missing value of ID) you can probably just use a FORMAT.

If you just want creation counts by month you can just use:

proc freq data=contact;
  where &startdate <= intnx('month',createddate,0,'b') <= &enddate ;
  tables createddate;
  format createddate monyy7.;
run;

But since you appear to want OVERLAPPING intervals you will need to use a multilevel format.

%LET StartDate = '01Jul2021'd;
%Let EndDate = '01Jun2023'd;

data format;
 fmtname='ASOF';
 hlo='SML';
 start=.;
 do offset=0 to intck('month',&StartDate., &EndDate.);
   end=intnx('month',&StartDate.,offset,'E');
   label=put(end,yymm7.);
   output;
 end;
run;

Now you can get your counts using a procedure like TABULATE, REPORT or MEANS that can use multilevel formats.

proc means data=contact nway noprint;
  class createddate / mlf ;
  format createddate asof.;
  output out=want n=Members;
run;

proc print;
  var createdate members;
run;

Re: SAS doesn't return expected table

PaigeMiller — Tue, 15 Aug 2023 09:22:05 GMT

@HG66 wrote:

Sorry my first day here so I'm still learning things. Here is the code I ran using what Tom suggested [I'll post the log shortly. I can't seem to include it here]

I also asked you to explain what you are trying to do, I don't see that. I still don't know what this confusing loop using macros is trying to do. Please explain in words. Please explain what the output values of w x y and z are, you haven't told us.

Re: SAS doesn't return expected table

HG66 — Tue, 15 Aug 2023 14:12:43 GMT

I have a startdate of Jul2021 and enddate of Jun2023. I want to:

Create a CONTACT table that has Id, and other fields with some WHERE conditions (the date lives here so I need something to loop from statdate to enddate). This could be a large table for each of the months between start and end dates or 23 tables for the 23 months.
COUNT table that counts the number of ID's in CONTACT table for each of the months.
Another table (which I did not mention in the post) from a different dataset A which will join the COUNT table and has WHERE conditions that looks into Id from CONTACT B (for example: where A. Contact in (select Id from B.CONTACT))

So desired results are

Date	Sum(A.Amount) – a field from dataset A	Count (A.Contact) - this depends on table CONTACT so that’s why I want a whole table for 23 months or 23 separate tables	Count (Id) for each of the months - Join the COUNT table on Date
Jul2021		10,000	100,000
…		…	…
June2023		15,000	120,000

Re: SAS doesn't return expected table

Quentin — Tue, 15 Aug 2023 14:20:53 GMT

Sorry, this is still hard to understand the big picture. Could you post the code to generate example input datasets, and then show the output you would want from that example data? Just a small example, so CARDS data steps, with maybe 3 months of data, and and 3-5 IDs for each month.

Re: SAS doesn't return expected table

PaigeMiller — Tue, 15 Aug 2023 14:32:06 GMT

Agreeing with @Quentin , this is still hard to comprehend. For example, you say:

Create a CONTACT table that has Id, and other fields with some WHERE conditions (the date lives here so I need something to loop from statdate to enddate). This could be a large table for each of the months between start and end dates or 23 tables for the 23 months.

You want code to create a table? Or do you already have data in a data set, and you want to do analysis on it? Even this basic understanding eludes me. Also, we have asked to see your data, can you show us a portion of the data? (Or do we have to help you create the data, which seems to be what you are saying)?

In any event, in this thread and future threads, please do not specify that a certain method be used (in this case, you have repeatedly said you want a loop). It may be that a loop is unnecessary, or that its inefficient. Just describe the problem clearly and completely, and ask for help with a solution.

Re: SAS doesn't return expected table

HG66 — Tue, 15 Aug 2023 14:49:25 GMT

@Quentin @PaigeMiller This is what i started with. For each of the Year&Month in Amount_By_Year table, I want the &members. for that period and Producers for that period (which is conditional on Contacts table). This code is not working as expected so that's why I need help. Thanks!

%let StartDate = '01Jul2021'd;
%let EndDate = '01Jun2023'd;

proc sql; 
create table Contacts as 
select Id, 
	   Name, 
           First_Issued_Policy__c
from Contact 
where ((RecordTypeId='hKQCQ' and CreatedDate<=&StartDate.)
     or (RecordTypeId='fgQCA' and CreatedDate<&StartDate. and (First_Issued_Policy__c>=&StartDate. or First_Issued_Policy__c is null))
;
quit;

proc sql; 
select count(Id) into :Members 
from Contacts;
quit;

proc sql; 
create table Amount_by_Year as 
select year(a.Record_Date__c) as Year, 
	   month(a.Record_Date__c) as Month, 
	   sum(b.Amount) as Amount, 
	   count(distinct b.Contact__c) as Producers, 
	   sum(b.Amount)/count(distinct b.Contact__c) as Avg_per_Producer,
	   &Members. as Members,
	   sum(b.Amount)/&Members. as Avg_per_Member
from Policy_Std a left join Policy_Agent_Std b on a.Id = b.Opportunity__c 
where b.Contact__c in (select Id from Contacts)
	and '01Jul2021'd <= a.Record_Date__c < '01Jul2023'd
group by 1, 2;
quit;

Re: SAS doesn't return expected table

PaigeMiller — Tue, 15 Aug 2023 14:49:45 GMT

Many of us refuse to download Microsoft Office documents as they can be security threats. I personally refuse to download ANY file attachments. Can you please show us the data, as text, for example following these instructions.

Re: SAS doesn't return expected table

HG66 — Tue, 15 Aug 2023 14:51:34 GMT

Yes. here it is

Id	Name	RecordTypeId	CreatedDate	First_Issued_Policy__c
OzQAO	John	hKQCQ	08APR2019:16:32:27	.
P4QAO	Michael	hJQCQ	08APR2019:19:14:37	.
PEQA4	Sam	fgQCA	09APR2019:10:28:26	.
PFQA4	Elia	fgQCA	09APR2019:10:28:26	.
PGQA4	Julie	fgQCA	09APR2019:10:28:26	3-Jun-19
PHQA4	Chris	fgQCA	29OCT2016:17:28:31	8-Jul-21
PIQA4	Tyler	fgQCA	09APR2019:10:28:26	21-Sep-22
PJQA4	Susan	fgQCA	09APR2019:10:28:26	.
PKQA4	Laura	fgQCA	09APR2019:10:28:26	24-Jan-20
PLQA4	Mike	fgQCA	09APR2019:10:28:26	11-Aug-22

Re: SAS doesn't return expected table

Quentin — Tue, 15 Aug 2023 16:24:38 GMT

Please post the input data as CARDS data steps, like:

data Contact ;
  input ID : $5. Name : $8. RecordTypeID : $5. CreatedDate : datetime. FirstIssued : date9.;
  format FirstIssued date9. CreatedDate datetime. ;
  cards ;
OzQAO  John     hKQCQ  08APR2019:16:32:27  .
P4QAO  Michael  hJQCQ  08APR2019:19:14:37  .
PEQA4  Sam      fgQCA  09APR2019:10:28:26  .
PFQA4  Elia     fgQCA  09APR2019:10:28:26  .
PGQA4  Julie    fgQCA  09APR2019:10:28:26  3-Jun-19
PHQA4  Chris    fgQCA  29OCT2016:17:28:31  8-Jul-21
PIQA4  Tyler    fgQCA  09APR2019:10:28:26  21-Sep-22
PJQA4  Susan    fgQCA  09APR2019:10:28:26  .
PKQA4  Laura    fgQCA  09APR2019:10:28:26  24-Jan-20
PLQA4  Mike     fgQCA  09APR2019:10:28:26  11-Aug-22
;
run ;

It looks like you have two other input datasets as well, Policy_Std and Policy_Agent_Std. Can you post them (as CARDS data steps)?

Then after you have posted the input datasets, please describe the output you want.