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

PROC SQL

SELECT
PUT(YR_2016,DATE9.)AS YR_2016;
PUT (YR_2015, DATE9.) AS YR_2015;
PUT (MONTH_START_DATE,DATE9.) AS MONTH_START_DATE;

DATA CALENDAR;
INFILE "//sas_env/empl/scm/Mahek/hw_query_calendar.csv" DELIMITER = ',' MISSOVER DSD LRECL=32767 FIRSTOBS=2 ;
INFORMAT CALENDAR_DATE MMDDYY10.;
INFORMAT FISCAL_YEAR 6.;
INFORMAT FISCAL_MONTH 6.;
INFORMAT FYQUARTER $6.;
INFORMAT MONTH_START_DATE MMDDYY10.;
FORMAT CALENDAR_DATE MMDDYY10.;
FORMAT FISCAL_YEAR 6.;
FORMAT FISCAL_MONTH 6.;
FORMAT FYQUARTER $6.;
FORMAT MONTH_START_DATE MMDDYY10.;
INPUT CALENDAR_DATE FISCAL_YEAR FISCAL_MONTH FYQUARTER $ MONTH_START_DATE ;
IF CALENDAR_DATE = TODAY();
RUN;

DATA CALENDAR2;
SET CALENDAR;
FORMAT QUERY_QTR $10.;
QUERY_QTR = "FY"||COMPRESS(SUBSTR(FYQUARTER,3,2))||' '||"Q"||COMPRESS(SUBSTR(FYQUARTER,6,1));
FORMAT YR_2016 MMDDYY10.;
PUT(YR_2016,DATE9.)AS YR_2016;
YR_2016 = MONTH_START_DATE - 364;
PUT (MONTH_START_DATE,DATE9.) AS MONTH_START_DATE;
FORMAT YR_2015 MMDDYY10.;
PUT (YR_2015, DATE9.) AS YR_2015;
YR_2015 = YR_2016 - 364;
RUN;

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

Are your existing YR_2015, YR_2016 and MONTH_START_DATE SAS date values?

 

The general run when applying a function or calculations to variables in SQL to create a new variable is

(calculation code) as resultingvariable name. If you use the SAME variable name the the variable TYPE will stay the same. You cannot change a variable type from numeric to character (or vice versa) in SAS. EVER.

 

put(yr_2015,date9) as Char_Yr_2015

would create a new text variable.

 

IN a data step the equivalent is

   Char_Yr_2015 = put(yr_2015,Date9.); <= Note that DATA step does not use AS to indicate the target variable. It is the name of the variable to the left of the =.

 

though I wonder why not using the Format date9. with the existing variable is not sufficient.

View solution in original post

4 REPLIES 4
Reeza
Super User

What is the question?

 

Your code is incorrect in many respsects just from a quick scan and I don't know where to start, so your question can help clarify the issue.Remember we can't run your code and I really don't want to read every line to highlight all your errors. 

At an absolute minimum include your log and clarify your question in detail please. 

 

When debugging fix the first error, re-run and then fix the next first error etc until everything works as expected.

Also, check every step and make sure it works before you move on, by every step I mean any PROC/DATA steps.

 

 

 

https://stackoverflow.com/help/how-to-ask

lbridges225
Calcite | Level 5

I want to convert YR_2015, YR_2016 and MONTH_START_DATE from date to character.  I am trying to use a PUT but not having any luck.  Someone said I need a new PROC SQL statement...but not sure how to begin...

 

DATA CALENDAR2;
SET CALENDAR;
FORMAT QUERY_QTR $10.;
QUERY_QTR = "FY"||COMPRESS(SUBSTR(FYQUARTER,3,2))||' '||"Q"||COMPRESS(SUBSTR(FYQUARTER,6,1));
FORMAT YR_2016 MMDDYY10.;
PUT(YR_2016,DATE9.)AS YR_2016;
YR_2016 = MONTH_START_DATE - 364;
PUT (MONTH_START_DATE,DATE9.) AS MONTH_START_DATE;
FORMAT YR_2015 MMDDYY10.;
PUT (YR_2015, DATE9.) AS YR_2015;
YR_2015 = YR_2016 - 364;
RUN;

ballardw
Super User

Are your existing YR_2015, YR_2016 and MONTH_START_DATE SAS date values?

 

The general run when applying a function or calculations to variables in SQL to create a new variable is

(calculation code) as resultingvariable name. If you use the SAME variable name the the variable TYPE will stay the same. You cannot change a variable type from numeric to character (or vice versa) in SAS. EVER.

 

put(yr_2015,date9) as Char_Yr_2015

would create a new text variable.

 

IN a data step the equivalent is

   Char_Yr_2015 = put(yr_2015,Date9.); <= Note that DATA step does not use AS to indicate the target variable. It is the name of the variable to the left of the =.

 

though I wonder why not using the Format date9. with the existing variable is not sufficient.

Reeza
Super User

That's absolutely not how you code in SAS. 

 

Start from the beginning, what do you have, what do you want and what have you tried. 

Your current code mixes SAS SQL, DATA step code and a bunch of other stuff that I'm not even sure what you're doing. 

 

Include your code that you currently have that works that you're trying to add on to or explain where you're starting from. 

I'm guessing you're new to SAS so you may also want to consider the training courses which are free.

 

Also, converting a date to a character variable is rarely a good idea in SAS. Usually only recommended when you're trying to export data.

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
  • 4 replies
  • 27041 views
  • 0 likes
  • 3 in conversation