BookmarkSubscribeRSS Feed
lnukr2
Calcite | Level 5

Given data:

Date           Company

20150101   Comp1

20150201   Comp2

20140301   Comp3.....

 

Desired output:

YYYY  MM  DD  Company

2015   01    01   Comp1

2015   02    01   Comp2

2014   03    01   Comp3.....

 

Its an excel file with 10000 data sets.

 

What code will transform the given data into desired output?

8 REPLIES 8
Astounding
PROC Star

Do you already know how to get your Excel files into a SAS data set, or is that part of the question?

 

Do you want your new fields as numeric or character?

lnukr2
Calcite | Level 5

Hi

I know to get excel into sas. I want to know the codes in SAS that can give me the desired output.

 

I want a character as well as numeric conversion codes.

 

Thanks.

Reeza
Super User

Whats the type (numeric/character) and format of the variable?

 

You can look at the YEAR/MONTH/DAY function in SAS if your variable is numeric with a date format. 

PS.  Why do you want to convert this? You can use all sorts of aggregation levels if you keep it as a SAS date.

lnukr2
Calcite | Level 5

Hi

 

Date value is numeric. 

Reeza
Super User

Whats the type (numeric/character) and format of the variable?

lnukr2
Calcite | Level 5

Its numeric date in YYYYMMDD format.

Reeza
Super User

Then use the YEAR/MONTH/DAY function to extract the relevant components.

 

See the relevant documentation under Date & Time Category:

https://support.sas.com/documentation/cdl/en/lefunctionsref/67960/HTML/default/viewer.htm#p0w6napahk...

 

Astounding
PROC Star

Well, I'm going to go out on a limb, and assume that the number within DATE is actually the 8-digit number that appears in your example (and not a SAS date).  In that case:

 

length yyyy_c $ 4 mm_c dd_c $ 2;

date_c = put(date, 8.);

yyyy_c = date_c;

mm_c = substr(date_c, 5, 2); 

dd_c = substr(date_c, 7, 2);

yyyy_n = input(yyyy_c, 4.);

mm_n = input(mm_c, 2.);

dd_n = input(dd_c, 2.);

 

If I'm wrong, and you actually have a SAS date stored within DATE, then use the suggestion about date functions.

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