topic Re: Creating individual month columns based on date observation values. in SAS Programming

Creating individual month columns based on date observation values.

JDDowell — Wed, 28 Aug 2019 18:27:29 GMT

Hello,

I'm trying to create a new column for every month of the year from values in a date column which is formatted as follows...

Date:

01/01/2008

02/01/2008

03/01/2008

10/01/2019

11/01/2019

12/01/2019

So every month of the year from '08-'19.

I would like all values of January to be in one columns, as well as columns for all the other individual months so that I can see the individual effects each month has on expenditure in a regression.

I thought that I might be able to search the date columns for the "MM/" part of MM/DD/YY and ignore the rest with a wildcard, then add those values to a new column called "MM", but I didn't have any luck.

I'm very new to SAS and might not have even known if I was searching for the right question, so apologies if this has been answered elsewhere.

Re: Creating individual month columns based on date observation values.

PaigeMiller — Wed, 28 Aug 2019 18:36:22 GMT

I assume there are other variables that we need to make use of when we re-arrange the data set.

Can you show us a more realistic input data set, and the desired output data set? In particular, this isn't clear to me: "I would like all values of January to be in one columns". Do you mean there is one January column regardless of year, or is there a Jan08 column and a Jan09 column and so on?

Re: Creating individual month columns based on date observation values.

Shmuel — Wed, 28 Aug 2019 18:39:04 GMT

1) Is your data in external file or a sas dataset ?

If it is external then the date is probably a CHAR type.
What format is the date: DD/MM/YYYY or MM/DD/YYYY ? It is not clear from your example dates.

If it is a sas dataset, is the date a sas numeric date ? Maybe what you see is formated display ?

2) why do you want each month in separate column ?

Do you want it in a report ? or in a new sas dataset?

3) Post a sample of your dataset in a INFILE DATALINES step, and post it in the running men icon window.
Post your code that you tried to run, the log and what issues did you have.

Re: Creating individual month columns based on date observation values.

JDDowell — Wed, 28 Aug 2019 20:32:38 GMT

1) I imported the data from an excel file and I'm calling on that SAS table that was created. In the SAS table it says the input is in MMDDYY10. format.

2) I would like to be able to regress expenditures on individual months.

Expenditure= Int + Jan + Feb + Mar +...+ Nov + residuals

The same data set is fine.

I used the import wizard rather than self input code.

DATA WORK.SupportGoods;
    LENGTH
        FY               $ 6
        MOS                8
        Type               8
        Eligible           8
        PC_12MoAVG         8
        Total              8
        TotNoEFC           8
        PCNoEFC_12MoAVG    8
        FYTotals           8
        FYEligible         8
        FYPerCap           8
        FYTotal            8
        FYTotNoEFC         8
        FYPC_NoEFC         8 ;
    FORMAT
        FY               $CHAR6.
        MOS              MMDDYY10.
        Type             BEST4.
        Eligible         BEST11.
        PC_12MoAVG       BEST11.
        Total            BEST11.
        TotNoEFC         BEST11.
        PCNoEFC_12MoAVG  BEST11.
        FYTotals         BEST4.
        FYEligible       BEST11.
        FYPerCap         BEST11.
        FYTotal          BEST11.
        FYTotNoEFC       BEST11.
        FYPC_NoEFC       BEST11. ;
    INFORMAT
        FY               $CHAR6.
        MOS              MMDDYY10.
        Type             BEST4.
        Eligible         BEST11.
        PC_12MoAVG       BEST11.
        Total            BEST11.
        TotNoEFC         BEST11.
        PCNoEFC_12MoAVG  BEST11.
        FYTotals         BEST4.
        FYEligible       BEST11.
        FYPerCap         BEST11.
        FYTotal          BEST11.
        FYTotNoEFC       BEST11.
        FYPC_NoEFC       BEST11. ;
    INFILE 'C:\Users\doweljd\AppData\Local\Temp\SEG1848\SupportGoods-0b48c160721c43eda7d6c60c32977a3c.txt'
        LRECL=143
        ENCODING="WLATIN1"
        TERMSTR=CRLF
        DLM='7F'x
        MISSOVER
        DSD ;
    INPUT
        FY               : $CHAR6.
        MOS              : ?? MMDDYY9.
        Type             : ?? BEST4.
        Eligible         : ?? COMMA11.
        PC_12MoAVG       : ?? COMMA11.
        Total            : ?? COMMA11.
        TotNoEFC         : ?? COMMA11.
        PCNoEFC_12MoAVG  : ?? COMMA11.
        FYTotals         : ?? BEST4.
        FYEligible       : ?? COMMA11.
        FYPerCap         : ?? COMMA11.
        FYTotal          : ?? COMMA11.
        FYTotNoEFC       : ?? COMMA11.
        FYPC_NoEFC       : ?? COMMA11. ;
RUN;

This was my idea of what would have created a new column for January if the date contained a substring for the month, but in retrospect if SAS stores it as a number I won't be able to search for a string value.

data s_g;
	set sg;
	if mos in ('01/') then Jan=Total;
	if mos gt '01nov2018'd then
		delete;
	keep mos total;
run;

The log doesn't give any errors, but the output data doesn't give anything either.

Re: Creating individual month columns based on date observation values.

ballardw — Wed, 28 Aug 2019 20:39:57 GMT

@JDDowell wrote:

Hello,

I'm trying to create a new column for every month of the year from values in a date column which is formatted as follows...

Date:

01/01/2008

02/01/2008

03/01/2008

.

.

.

10/01/2019

11/01/2019

12/01/2019

So every month of the year from '08-'19.

I would like all values of January to be in one columns, as well as columns for all the other individual months so that I can see the individual effects each month has on expenditure in a regression.

I thought that I might be able to search the date columns for the "MM/" part of MM/DD/YY and ignore the rest with a wildcard, then add those values to a new column called "MM", but I didn't have any luck.

I'm very new to SAS and might not have even known if I was searching for the right question, so apologies if this has been answered elsewhere.

Is this for a report that people read or do you need a data set? Generally Reports show this sort of across behavior while data sets maintain a single record per date.

Re: Creating individual month columns based on date observation values.

JDDowell — Wed, 28 Aug 2019 20:45:08 GMT

You are correct, I would like for there to be a single January column containing different 'Total' values, a single column for February and so on.

Re: Creating individual month columns based on date observation values.

JDDowell — Wed, 28 Aug 2019 21:00:41 GMT

The more I talk about it the less certain I'm getting about if I'm even going about my question in the right way, but I'll try to explain my reasoning anyway.

My belief is that certain months have higher expenditures (the totals column) than others, and that this is consistent over the years. If I regress across the entire timeline then it is my understanding that I'm just going to forecast an average increase, and I won't be able to show these seasonal jumps that I perceive.

Re: Creating individual month columns based on date observation values.

ballardw — Wed, 28 Aug 2019 21:05:10 GMT

@JDDowell wrote:

The more I talk about it the less certain I'm getting about if I'm even going about my question in the right way, but I'll try to explain my reasoning anyway.

My belief is that certain months have higher expenditures (the totals column) than others, and that this is consistent over the years. If I regress across the entire timeline then it is my understanding that I'm just going to forecast an average increase, and I won't be able to show these seasonal jumps that I perceive.

Do you have access to SAS/ETS? That does timeseries analysis of various flavors that account for things like seasonality (increased sales of snow skiing equipment in Fall vs spring for example).

Those procedures understand dates for such things and no reshaping should be needed though you may need to specify the type of interval for collection (monthly instead of daily for instance).

Re: Creating individual month columns based on date observation values.

JDDowell — Wed, 28 Aug 2019 21:33:31 GMT

Unfortunately I do not think my agency has a license for SAS/ETS, but I will email our IT. I was able to use PROC ARIMA to do time series analysis and was able to identify and adjust the model to a fair degree, but I don't entirely believe that I'm capturing the seasonal effect in my forecasted values. So I was trying to just do individual months.

I'm a few years removed from school, so my econometrics knowledge has degraded. Perhaps this is not the best way to tackle this issue.

Re: Creating individual month columns based on date observation values.

JDDowell — Wed, 28 Aug 2019 22:08:54 GMT

I'm an idiot. I just realized I needed to change a parameter to get the desired monthly seasonality in my original ARIMA model. Thank you for your help.

Re: Creating individual month columns based on date observation values.

ballardw — Thu, 29 Aug 2019 18:31:07 GMT

@JDDowell wrote:

I'm an idiot. I just realized I needed to change a parameter to get the desired monthly seasonality in my original ARIMA model. Thank you for your help.

Proc Arima is in SAS/ETS, so apparently you have that module.