BookmarkSubscribeRSS Feed
ilikesas
Barite | Level 11

Hi,

Suppose I want to create 100 imaginary companies, say COMP1 - COMP100,

and for each company I need a certain number of dates, say 1/1/2001 to 1/1/2010, and to create a new column to id the date, i.e, 1 corresponding to 1/1/2001 ... 10 corresponding to 1/1/2010

So here I have 100 * 10 = 1000 company/date combinations.

Please help me write a code that can incorporated any number of companies and dates

Thank you very much!!!

3 REPLIES 3
ballardw
Super User

using an id variable for the companies

data want;

     do companyID = 1 to 100; /* change 100 to what ever you want */

          dateid=0;

          do date = '01JAN2001'd to '01JAN2010'd;  /* note use of date literals, MUST be in the form of 'ddMMMyyy'd */

               dateid+1;

               output;

          end;

     end;

     format date mmddyy10.;

run;

ilikesas
Barite | Level 11

Hi ballardw,

thank you for replying, I ran your code and it worked perfectly.

But could you tell me please how to make the date id's go by year, i.e, from 1 to 365, like this repeating themselves every year regardless of the company.

Also, how is it possible to change the time frequency, for example to go from daily to weekly or monthly?

Thank you

ballardw
Super User

Here's an example of ways to get some of the interval ids you mention.

data want;

     do companyID = 1 to 100; /* change 100 to what ever you want */

          do date = '01JAN2001'd to '01JAN2010'd;  /* note use of date literals, MUST be in the form of 'ddMMMyyy'd */

               /* all of these interval ids reset each calendar year though at different places*/

               dateid = mod(juldate(date),1000);

               weekid1 = week(date,'U');/* sunday as first day of week*/

               weekid2 = week(date,'V');/* monday first day of week but first week depends on how january starts*/

               weekid3 = week(date,'W');/* monday first day of week */

               monthid = month(date);

               output;

          end;

     end;

     format date mmddyy10.;

run;

With week you need to consider how you define week: Which day does a week typically start on, Sunday (which is default with SAS but can be changed).

Are the Weeks monthly dependent? Consider week of 27 April 2014 to 3 May 2014. Would want 1 May to start the week for May? If so would the next week start on Sunday, 4 May or run for 7 days? Also there are considerations for beginning and end of year. For instance December 2014 ends on a Wednesday. Do you want the period of 28 Dec to 3 Jan 2015 to be the last week of 2014 or the first week of 2015?

So which DATE for each week do you want?

With a monthly interval do you want the date output to be the first day of the month, the end of the month or something in between? Also do you mean calendar month or a 4-week interval?

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 948 views
  • 0 likes
  • 2 in conversation