Create time ID variable with program (instead of using the point-and-click system)

[Rens]

Dear SAS users,

SAS offers a point-and-click system to create a time ID variable from a certain starting date using a particular frequency (e.g. weeks, quarters, years).

Since I need to do this proces repeatedly, I like to use a code as it makes things much easier. My data covers 1985-2005 and is divided into quarters (which gives 21 years * 4 quarters = 84 observations).

The date variable column should look like this (or any other number which can be formated into a date):

Date:

1985/1

1985/2

1985/3

1985/4

etc.

Does anyone know how to write a code for this?

Thank you very much in advance!

Rens (a postgraduate student in sociology)

[RW9]

Hi,

I have no idea about a point & click system, or what software you are using, but in base SAS you could do:

data want;
  do year=1985 to 2005;
    do quater=1 to 4;
      year_quarter=cats(put(year,4.),"/",put(quater,1.));
      output;
    end;
  end;
run;

So a loop over your years, and then for each year, a loop over the quaters.  It may be possible to shrink the code somewhat, or use a procedure, but I feel the above code shows the process more clearly.

[slangan]

You can use a YYQxw. format. I think that yyqs8. looks a lot like the format you want. The data will stay as a SAS date value, but it gives you the desired look.

https://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002231398.htm

[RW9]

Yes, I never have to deal with quaters, so I forget those formats.   To change the loop slightly to get:

data want;
  do year_qtr=input("1985Q01",yyq9.) to input("2005Q04",yyq9.);
      output;
  end;
run;

[Rens]

Thank you very much. This second loop does does create a sas date variable (which can be formated like i want), but does it for every day. So I get more than 600000 observation, whereas only 84 are needed.

The first loop creates a file with only 84 observation, but the 'year_quarter' variable is not formattable as a sas date. Normally I indeed use the yyqs8. format to change a sas date into the desired form (like this: format year_quarter yyqs8.; )

Is there perhaps a solution? Many thanks again.

[RW9 · Accepted solution]

Well, my original code was creating a text field that looked like the data you had provided.  If you are ok with the SAS quarter value then change to:

data want;
 do year=1985 to 2005;
   do quater=1 to 4;
     year_quarter=input(cats(put(year,4.),"Q",put(quater,1.)),yyq8.);
     output;
   end;
 end;
 format year_quarter yyq8.;
run;

As for the second code, will need to have a look, perhaps a by group or something.  Will post when I get a chance to look at it.

[Rens]

Thanks! This is the solution. With this loop I can create a date ID variable and, subsequently, merge all my observations into it. I just checked to be sure, but the SAS time series procedures also recognize this date variable for different types of analyses.