Create a column "Month" for all the months

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 112
Accepted Solution

Create a column "Month" for all the months

Hello everyone,

 

My data set is like below:

 

Sep Oct Nov Dec Jan Feb Mar April May

x      y    z      L      K    A     B    C      D

 

Now I hope to create a column as:

 

Month

 

Sep

Oct

Nov

Dec 

Jan

Feb

April

May

 

 

If I am going to use Transpose,

but there is not a variable named as "Month"

How can I create this variable and put the existing month in it?

 

Thank you~~


Accepted Solutions
Solution
‎03-03-2018 03:51 PM
PROC Star
Posts: 1,836

Re: Create a column "Month" for all the months

Posted in reply to novinosrin

Just to complete your request:

 

data have;
infile datalines expandtabs truncover;
input Obs	Year	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May;
datalines;
1	1884	0	0.0	1.0	27.1	22.2	17.0	3.5	19.5	0.0
2	1885	0	1.7	8.2	8.4	16.9	16.0	6.5	7.0	0.0
3	1886	0	0.0	22.2	12.5	12.0	18.4	6.3	1.2	0.0
4	1887	0	0.2	2.2	9.3	21.3	4.1	13.2	0.4	0.0
5	1888	0	0.0	4.0	15.5	17.8	22.0	17.5	5.4	0.0
6	1889	0	0.0	5.7	6.1	20.2	14.8	19.0	0.0	0.0
7	1890	0	0.0	2.1	29.2	16.1	24.6	12.2	0.3	0.1
8	1891	0	0.1	9.7	4.7	26.4	10.3	25.1	0.8	0.0
9	1892	0	0.0	14.0	19.2	15.9	29.8	8.1	9.6	0.0
10	1893	0	0.5	6.1	27.6	20.0	29.5	5.4	13.3	0.0
11	1894	0	0.0	11.1	22.1	26.5	23.6	9.5	0.6	0.0
12	1895	0	1.5	5.9	8.7	22.5	39.1	45.1	1.0	0.0
13	1896	0	0.0	5.5	13.9	20.1	13.7	8.1	5.2	0.0
14	1897	0	0.0	10.1	18.4	32.1	26.8	1.2	2.4	0.0
15	1898	0	0.0	10.6	27.0	16.6	16.3	21.2	4.3	0.0
16	1899	0	0.0	1.3	21.5	24.7	28.5	54.0		
;


proc transpose data=have out=want name=month;
by year;
var sep--may;
run;

View solution in original post


All Replies
Super User
Posts: 23,773

Re: Create a column "Month" for all the months

If that's the column names you don't need to specify it. 

 

Try a TRANSPOSE without anything else

 

proc transpose data=have out=want;
run;
Frequent Contributor
Posts: 112

Re: Create a column "Month" for all the months

hello,

 

Thanks for replyingSmiley Happy

 

The data looks like the attached.

My code:

data snow1;
set "C:\Users\jc3992\Downloads\snow1.sas7bdat";
proc print data=snow1;
title "snow1";
run;
proc transpose data=snow1 out=snow1a;
by year;
var month=sep--may;
run;

The LOG showed:

 

166  proc transpose data=snow1 out=snow1a;
167  by year;
168  var month=sep--may;
              -
              22
              200
ERROR: Variable MONTH not found.
ERROR 22-322: Syntax error, expecting one of the following: a name, ;, -, :, _ALL_,
              _CHARACTER_, _CHAR_, _NUMERIC_.
ERROR 200-322: The symbol is not recognized and will be ignored.
169  run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.SNOW1A may be incomplete.  When this step was stopped there were 0
         observations and 0 variables.
WARNING: Data set WORK.SNOW1A was not replaced because this step was stopped.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.33 seconds
      cpu time            0.01 seconds


PROC Star
Posts: 1,836

Re: Create a column "Month" for all the months

data have;
length Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May 8;
run;

proc transpose data=have(obs=0) out=want name=month;
var sep--may;
run;
Frequent Contributor
Posts: 112

Re: Create a column "Month" for all the months

Posted in reply to novinosrin

Hello,

 

thanks for helping me out >"<

 

I used this:

%let dirdata=C:\Users\jc3992\My SAS Data;
libname Test "&dirdata";
run;

data snow1;
set "C:\Users\jc3992\Downloads\snow1.sas7bdat";
proc print data=snow1;
run;

data work.snow1;
length Sep Oct Nov	Dec	Jan	Feb	Mar	Apr	May 8;
run;

proc transpose data=work.snow1(obs=118) out=test.snow1a name=month prefix=snowfall;
var sep--may;
by year;
run;

proc print data=snow1a;
run;

The LOG:

 

275  %let dirdata=C:\Users\jc3992\My SAS Data;
276  libname Test "&dirdata";
NOTE: Libref TEST refers to the same physical library as DIRDATA.
NOTE: Libref TEST was successfully assigned as follows:
      Engine:        V9
      Physical Name: C:\Users\jc3992\My SAS Data
277  run;
278
279  data snow1;
280  set "C:\Users\jc3992\Downloads\snow1.sas7bdat";

NOTE: There were 118 observations read from the data set
      C:\Users\jc3992\Downloads\snow1.sas7bdat.
NOTE: The data set WORK.SNOW1 has 118 observations and 10 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


281  proc print data=snow1;
282  run;

NOTE: There were 118 observations read from the data set WORK.SNOW1.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.04 seconds
      cpu time            0.04 seconds


283
284  data work.snow1;
285  length Sep Oct Nov  Dec Jan Feb Mar Apr May 8;
286  run;

NOTE: Variable Sep is uninitialized.
NOTE: Variable Oct is uninitialized.
NOTE: Variable Nov is uninitialized.
NOTE: Variable Dec is uninitialized.
NOTE: Variable Jan is uninitialized.
NOTE: Variable Feb is uninitialized.
NOTE: Variable Mar is uninitialized.
NOTE: Variable Apr is uninitialized.
NOTE: Variable May is uninitialized.
NOTE: The data set WORK.SNOW1 has 1 observations and 9 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


287
288  proc transpose data=work.snow1(obs=118) out=test.snow1a name=month prefix=snowfall;
289  var sep--may;
290  by year;
ERROR: Variable YEAR not found.
291  run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set TEST.SNOW1A may be incomplete.  When this step was stopped there were 0
         observations and 0 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

292


293  proc print data=snow1a;
294  run;

NOTE: There were 9 observations read from the data set WORK.SNOW1A.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


The output SNOW1A still is unsuccessful...

 

PROC Star
Posts: 1,836

Re: Create a column "Month" for all the months

@jc3992 , I was just playing to show a simple demonstration. I get the impression you need to really get the hang of all the basic concepts of SAS language datastep/proc so that you can feel comfortable. Don't worry. I was like you 3-4 years ago. We all learn. I'd suggest get some books and read to familiarize the concepts.

 

Let me request Reeza, as she often helps out folks with some links to video tutorials/ other related links. @Reeza  ,Can you please share those links the OP may use to come up to speed?

Solution
‎03-03-2018 03:51 PM
PROC Star
Posts: 1,836

Re: Create a column "Month" for all the months

Posted in reply to novinosrin

Just to complete your request:

 

data have;
infile datalines expandtabs truncover;
input Obs	Year	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May;
datalines;
1	1884	0	0.0	1.0	27.1	22.2	17.0	3.5	19.5	0.0
2	1885	0	1.7	8.2	8.4	16.9	16.0	6.5	7.0	0.0
3	1886	0	0.0	22.2	12.5	12.0	18.4	6.3	1.2	0.0
4	1887	0	0.2	2.2	9.3	21.3	4.1	13.2	0.4	0.0
5	1888	0	0.0	4.0	15.5	17.8	22.0	17.5	5.4	0.0
6	1889	0	0.0	5.7	6.1	20.2	14.8	19.0	0.0	0.0
7	1890	0	0.0	2.1	29.2	16.1	24.6	12.2	0.3	0.1
8	1891	0	0.1	9.7	4.7	26.4	10.3	25.1	0.8	0.0
9	1892	0	0.0	14.0	19.2	15.9	29.8	8.1	9.6	0.0
10	1893	0	0.5	6.1	27.6	20.0	29.5	5.4	13.3	0.0
11	1894	0	0.0	11.1	22.1	26.5	23.6	9.5	0.6	0.0
12	1895	0	1.5	5.9	8.7	22.5	39.1	45.1	1.0	0.0
13	1896	0	0.0	5.5	13.9	20.1	13.7	8.1	5.2	0.0
14	1897	0	0.0	10.1	18.4	32.1	26.8	1.2	2.4	0.0
15	1898	0	0.0	10.6	27.0	16.6	16.3	21.2	4.3	0.0
16	1899	0	0.0	1.3	21.5	24.7	28.5	54.0		
;


proc transpose data=have out=want name=month;
by year;
var sep--may;
run;
Frequent Contributor
Posts: 112

Re: Create a column "Month" for all the months

Posted in reply to novinosrin
proc sort data=test.snow1a;
by month;
run;

proc transpose data=test.snow1a out=test.snow1a2(drop=_name_);
by Month;
id year;
where year>1991;
run;

proc print data=test.snow1a2;
run;

Now I am going to print out the last 10 years data set

Mine graph is as attached.

Do you think I can do anything for a column name as "Year"

Mine looked a bit not nice and neat 

 

Thank you for your help!!

Frequent Contributor
Posts: 112

Re: Create a column "Month" for all the months

Posted in reply to novinosrin

Thank you very very much!!!

 

The thing is I have too little time for familiarizing SAS,

and we have assignment every week

(I also got other subjects with tests and assignments every week >"<)

 

Thanks a lot!

I watched some vid on Youtube too

it would be great if you guys can help me out more on this.

I found a Youtube channel titled "SAS ...for true beginners " that was really helpful

sadly the owner did not upload anything anymore after four vids

Hope you both can offer us beginners some guidance!Smiley Happy 

PROC Star
Posts: 1,836

Re: Create a column "Month" for all the months

Always willing to help(it's just i'm just too lazy i'm afraid). Pardon me, I meant the above comment more on the affirmative and not to point your weakness.  All the best for your assignments and I hope you do well. Have a great weekend.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 187 views
  • 4 likes
  • 3 in conversation