topic Re: Month Order on sas guide in SAS Enterprise Guide

Month Order on sas guide

peter2 — Mon, 01 Aug 2016 10:36:03 GMT

Hello,

I use SAS GUIDE and i want to classify datas by month.

My table is Janv-15 to dec-15...or SAS class by alphabet instead or chronologic...it put avril then august insteal of janvier, février...

What do i have to do?

Thanks

Re: Month Order on sas guide

Kurt_Bremser — Mon, 01 Aug 2016 11:04:41 GMT

You either have your dates stored as character instead of as SAS date values, or you're sorting by formatted instead of raw values.

Re: Month Order on sas guide

peter2 — Mon, 01 Aug 2016 11:53:31 GMT

Hello, here is my table...how can i put this by chronology?

Re: Month Order on sas guide

Reeza — Mon, 01 Aug 2016 12:03:09 GMT

Make sure your dates are SAS dates, so numeric with a date format. Then they'll sort correctly. You may need t create a new variable to do this using INPUT function.

Re: Month Order on sas guide

peter2 — Mon, 01 Aug 2016 12:26:29 GMT

@Reeza, thanks, i do this but problem :

3 PROC SQL NOEXEC;

4 SELECT /* mois num */

5 (INPUT(t1.MOIS,CATDFMY)) AS 'mois num'n

_______

ERROR 22-322: Syntax error, expecting one of the following: un nom de format, ?.

ERROR 76-322: Syntax error, statement will be ignored.

6 FROM WORK.TABLE_DES_MOIS1 t1;

7 QUIT;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 secondes

cpu time 0.00 secondes

8 QUIT; RUN;

here is my table

01/01/2015 janv.-15
02/01/2015 janv.-15
03/01/2015 janv.-15
04/01/2015 janv.-15
05/01/2015 janv.-15
06/01/2015 janv.-15
07/01/2015 janv.-15
08/01/2015 janv.-15
09/01/2015 janv.-15
10/01/2015 janv.-15
11/01/2015 janv.-15
12/01/2015 janv.-15
13/01/2015 janv.-15
14/01/2015 janv.-15
15/01/2015 janv.-15

Re: Month Order on sas guide

Kurt_Bremser — Mon, 01 Aug 2016 12:39:10 GMT

data work.new;
set WORK.TABLE_DES_MOIS1;
mois_num = input(MOIS,ddmmyy10.);
run;

Don't forget the dot when using a format.

Re: Month Order on sas guide

peter2 — Mon, 01 Aug 2016 12:57:01 GMT

@Kurt, thanks but it gimes now

1 ;*';*";*/;quit;run;

2 OPTIONS PAGENO=MIN;

3 PROC SQL NOEXEC;

4 SELECT /* Mois NUM */

5 (Mois_Num = INPUT(t1.MOIS,ddmmyy10.)) AS 'Mois NUM'n

6 FROM WORK.TABLE_DES_MOIS1 t1;

ERROR: The following columns were not found in the contributing tables: Mois_Num.

7 QUIT;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 secondes

cpu time 0.00 secondes

8 QUIT; RUN;

Re: Month Order on sas guide

TomKari — Mon, 01 Aug 2016 13:33:32 GMT

It would be well worth reading this for background.

http://support.sas.com/documentation/cdl/en/lrcon/68089/HTML/default/viewer.htm#n0q9ylcaccjgjrn19hvqnd9cte8p.htm

Re: Month Order on sas guide

Reeza — Mon, 01 Aug 2016 13:50:59 GMT

Your calculation s mixing data step and SQL.

Input(..) as 'mois num'n

No = or variable name before the Input.

Re: Month Order on sas guide

Kurt_Bremser — Mon, 01 Aug 2016 14:13:38 GMT

You need to write correct SQL code, of course. I gave you a data step.

proc sql;
select
  input(t1.mois,ddmmyy10.) as mois_num
from work.table_des_mois t1
;
quit;

Note that I deliberately do not use blanks in variable (or other) names, as they are only a major pain-in-the-ass.

Re: Month Order on sas guide

peter2 — Mon, 01 Aug 2016 14:24:47 GMT

@Reeza, in fact!!

I make a modification

INPUT(t1.MOIS,monyy5.)

But it gives me "."

. 01/01/2015 janv.-15
. 02/01/2015 janv.-15
. 03/01/2015 janv.-15
. 04/01/2015 janv.-15
. 05/01/2015 janv.-15
. 06/01/2015 janv.-15

Re: Month Order on sas guide

Kurt_Bremser — Mon, 01 Aug 2016 14:33:35 GMT

@peter2 wrote:

@Reeza, in fact!!

I make a modification

INPUT(t1.MOIS,monyy5.)

But it gives me "."

. 01/01/2015 janv.-15
. 02/01/2015 janv.-15
. 03/01/2015 janv.-15
. 04/01/2015 janv.-15
. 05/01/2015 janv.-15
. 06/01/2015 janv.-15

monyy. is a standard SAS format and expects English month abbreviations.

For the second column, use ddmmyy10.

Re: Month Order on sas guide

peter2 — Mon, 01 Aug 2016 14:35:10 GMT

in fact, i have OCT2015 & NOV2015....but not for the other month, why?

Re: Month Order on sas guide

Kurt_Bremser — Mon, 01 Aug 2016 14:41:17 GMT

Up to now, you only posted example data for January. Providing more complete example data will make it easier for us to help you.

What exactly IS the rule for building your target variable, and what values should it contain? Keep in mind that a SAS date value is a count of the number of days since 01/01/1960 and will always contain a "day part".

Re: Month Order on sas guide

peter2 — Mon, 01 Aug 2016 14:51:38 GMT

thank you all...

@Kurt, here is what i obtain

. 25/09/2016 sept.-16
. 26/09/2016 sept.-16
. 27/09/2016 sept.-16
. 28/09/2016 sept.-16
. 29/09/2016 sept.-16
. 30/09/2016 sept.-16
OCT2016 01/10/2016 oct.-16
OCT2016 02/10/2016 oct.-16
OCT2016 03/10/2016 oct.-16
OCT2016 04/10/2016 oct.-16
OCT2016 05/10/2016 oct.-16
OCT2016 06/10/2016 oct.-16
OCT2016 07/10/2016 oct.-16

what i want is to get in the first row all months like OCT2016 in the aim to class by chronology.

Re: Month Order on sas guide

Kurt_Bremser — Tue, 02 Aug 2016 05:23:02 GMT

For a standard SAS date format, the valid mont names are these:

jan

feb

mar

apr

may

jun

jul

aug

sep

oct

nov

dec

That's why it recognizes oct, but not sept.

Why don't you use the dates that come in a standard format as base for your calculation?

month_date = input('01'!!substr(date_char,3),ddmmyy10.);

month_date = input(char_date,ddmmyy10.);
month_date = intnx('month',month_date,0,'begin');

Re: Month Order on sas guide

peter2 — Wed, 03 Aug 2016 13:58:00 GMT

@Kurt_Bremser I'm sorry, my level is so low..i try to undertand but it's hard...i still dont succes to class by chronological month...

Re: Month Order on sas guide

peter2 — Wed, 03 Aug 2016 14:49:32 GMT

@Kurt_Bremser; Finally I have changed my source table for the month. I transform the csv table in excel and sas keeps the data I set. I put the date of month in date and in informat MONYY7 and...it's OK!!

I think I had 2 problems: SAS change what I write in csv (no in excel) and the personalised format instead of date format...

Thanks you all a lot to help me...

Re: Month Order on sas guide

Kurt_Bremser — Thu, 04 Aug 2016 05:14:54 GMT

The Excel file format is probably the worst way to import data into SAS. Especially the old xls is an undocumented, disorganized dungheap that barely works. How often did I need to open MS Office files in OpenOffice just to make them usable again!

.csv is a textual format that can easily be inspected with a text editor to control what is in there and how it was stored.

There are formats in SAS that support local conventions, see SAS NLS Reference Guide.

But I always prefer to use formats that are common on an international level, like YYYY-MM-DD (which is the default format that DB/2 uses for displaying dates).

Re: Month Order on sas guide

peter2 — Thu, 04 Aug 2016 08:51:58 GMT

@Kurt_Bremser, csv is maybe the best...but i turn around in your explication.

My table on csv give me only french date as janv-15, fevr-15...format that SAS dont recognize...so csv put french format that sas can't translate...so I'm blocked.

If you get time, it would be nice to modify my table csv in format SAS know...whether i 'll let in excel...

Thank you