SAS doesn't return expected table

[HG66]

%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.

[Tom]

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?

[HG66]

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

Date         |  Members

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

Jul2021    | x

Aug2021  | y

Sep2021  | z

.

.

.

Jun2023 | w

[HG66]

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.)

[Tom]

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.)

[HG66]

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?

[PaigeMiller]

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.

[HG66]

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.)

[Quentin]

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.

[PaigeMiller]

---

@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.

[HG66]

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

1. 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.
2. COUNT table that counts the number of ID's in CONTACT table for each of the months.
3. 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

[Quentin]

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. 

[PaigeMiller]

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

1. 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.

[HG66]

@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; 

[PaigeMiller]

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.