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

Hi

 

I have my data for a variable in "csv" file where each column is a Quarterly date from year 1997 to 2016 (e.g. date format is: 03/31/1997 06/30/1997 09/30/1997 12/31/1997), and each row is a company (e.g. CUSIP). I want to transpose this data in format below:

CUSIP   Date_Quarter   Variable

001         03/31/1997        2.3

001         06/30/1997        5.0

001         09/30/1997        4.78

001        12/31/1997         4.5

002         03/31/1998        6.6

002         06/30/1998        9.0

002         09/30/1998        8.7

002        12/31/1998         5.6   and so on.

 

I have imported the original data file in SAS, which is making Dates as Column Names and now they look like this: "_03_31_1997". This means SAS is not considering them as Dates. I have applied the following code to get required data set;

proc transpose data=have out=want;
by CUSIP ; run;

 

This gives me accurate results, but the  only issue is with formatting of Dates. The Date column is a Character right now with format and informat as "$11".

 

Please guide me how to get dates format as mmddyy10. 

 

Thanks.

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

My apologies. You don't need the put function wrapper, and that's what converts back to char again. Please try this:

data final_want;

set want;

corrected_date1=input(compress(date,'_'), mmddyy10.);
format corrected_date1 mmddyy10.;
run;

 

Notes: Instead of put function to apply format, we are using the format statement to keep the variable numeric and just the format appearance of mmddyy10. is applied. Sorry again

View solution in original post

4 REPLIES 4
novinosrin
Tourmaline | Level 20

If i understand you, you just need your char date to be read into a sas date value and apply mmddyy10. format?

 

if yes, and all your date values are char date values in the pattern "_03_31_1997";, you could try the following

 


data final_want;
set want;
corrected_date=put(input(compress(date,'_'), mmddyy10.),mmddyy10.);
run;

 

/* Drop your old date column with _03_31_1997 kinda values*/

 

 

Saba1
Quartz | Level 8

@novinosrin Thanks a lot. By using your code, I can change the appearance of Date, i.e. it looks as 12/31/1997 but it is still "Character" with format and informat as $10. How can I change the format to be mmddyy10.?

novinosrin
Tourmaline | Level 20

My apologies. You don't need the put function wrapper, and that's what converts back to char again. Please try this:

data final_want;

set want;

corrected_date1=input(compress(date,'_'), mmddyy10.);
format corrected_date1 mmddyy10.;
run;

 

Notes: Instead of put function to apply format, we are using the format statement to keep the variable numeric and just the format appearance of mmddyy10. is applied. Sorry again

Saba1
Quartz | Level 8

@novinosrin Thanks. The format is exactly the same as required. Thanks a lot.

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
  • 3253 views
  • 0 likes
  • 2 in conversation