BookmarkSubscribeRSS Feed
LeroyA
Calcite | Level 5

Hi all,

 

I'm relatively new to SAS, have completed a beginner a course on EG 6.1 and Miner.   I've looked around the forum but don't seem to find a solution to a specific problem I'm faced with.

 

I have imported a dataset, say contract dates. Each observation has a start date, and an end date if the contract was terminated.  I want to count the number of observations (active and/or ended) on different time frames, f.i. last 60 months, and create an overview like a summary table.

 

Obs   Start                        End

1        Jan 1, 2015               -

2        March 15, 2010      April 20, 2012

3        October 4, 2013     October 4, 2015 

 

The only method I currently see is to create a computed column that compares the observation start/end date to the reference date (1: active on date or during period, 0: inactive) and then summarizing it,  but this would mean having to create dozens of computed columns with fixed reference dates to build up a history. 

 

Obs   Start                        End                       Jan 1, 2015     Nov 1, 2015

1        Jan 1, 2015               -                                  1                    1

2        March 15, 2010      April 20, 2012                0                    0

3        October 4, 2013     October 4, 2015             1                   0

 

I'm struggling to find an easier solution in SAS EG. Can someone point me in the right direction or a similar topic on how to handle this?

 

Many thanks in advance!

 

 

 

7 REPLIES 7
Kurt_Bremser
Super User
%let start='01jan2016'd;
%let end=%sysfunc(date());

proc sql;
select count(*) from have
where start < &end and (end > &start or end = .)
;
quit;

So you don't need extra variables.

LeroyA
Calcite | Level 5

Thank you for the quick reply, Kurt.   SAS programming is still very new to me.  I'll try to find out what that code does and how to use it before posting a new question.

LinusH
Tourmaline | Level 20
You could do this in the Query Builder. But with this short SQL it's probably easier just to use the SQL as a program...
Data never sleeps
Ksharp
Super User

It should be easy for SQL.

CODE NOT TESTED.

proc sql;
select count(*) as n_active 
 from have
   having start is not missing and end is missing and
              start ge intnx('month',today(),-60,'s');

select count(*) as n_end 
 from have
   having start is not missing and end is not missing and
              start ge intnx('month',today(),-60,'s');
quit;

LeroyA
Calcite | Level 5

I tried to adapt your codes and apply it on my database. It runs without errors and returns a result: a single number for Kurt's code and 3 million+ identical totals for KSharp's code.  But as I require a comprehensive overview with much more detail (such as evolutions by month by different categories such as geography, age....) , I don't think I will be able to work this out in coding yet with the limited knowledge I have. 

 

I'm totally new to programming, even SQL is not very common to me. I'm basically an end-user with limited IT knowledge. It was told that EG does not require any knowledge of coding, but almost every solution offered on various questions in the forum comes down to coding. So I guess I'll need to take a course on that...  

 

Anyway, thank you for your time and suggestions!

 

 

 

 

Patrick
Opal | Level 21

SAS EG has a Point & Click interface which allows you to get quite a few things done without any coding (the UI actually generates the code in the background).

 

A lot of the people here in this forum are "coders". You need to specifically ask how things could be done with EG point & click if you need such an answer.

 

When things become more complicated though then you'll reach the limit of point & click and you need to code and if you have some coding skills then also using the point & click option becomes more powerfull as sometimes you can ingest code blocks (functions, formats etc.) which opens up additional possibilities for you.

 

So yes, absolutely, go for training. There are recommended learning paths depending on where you come from and want to be 

https://support.sas.com/training/us/paths/ 

ballardw
Super User

It will also help if show the desired final output, not just that of what you think an intermediate data set should look like.

Sometimes you can find that the Reporting procedures are very powerful for creating complex summaries. Though usually more than 3 or 4 rows of data may be needed.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 7 replies
  • 1231 views
  • 3 likes
  • 6 in conversation