BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jc3992
Pyrite | Level 9

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~~

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

9 REPLIES 9
Reeza
Super User

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;
jc3992
Pyrite | Level 9

hello,

 

Thanks for replying:)

 

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


novinosrin
Tourmaline | Level 20
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;
jc3992
Pyrite | Level 9

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...

 

novinosrin
Tourmaline | Level 20

@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?

novinosrin
Tourmaline | Level 20

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;
jc3992
Pyrite | Level 9
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!!

jc3992
Pyrite | Level 9

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!:) 

novinosrin
Tourmaline | Level 20

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.

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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