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

 

Hi,

 

I am using below condition for extracting the age column. I am getting wrong output. 

Could someone help me how to fix this:

1. output has age group and date which is wrong, it should have only age grouping.

2. how to remove trailing blanks in 999 value 

 

 

 

trim(put(case 
    when yrdif(input(Dob ,yymmdd10.),input(Date ,yymmdd10.),'age') = . then 999
    when     yrdif(input(Dob ,yymmdd10.),input(Date ,yymmdd10.),'age') >=  0 
    then floor(yrdif(input(Dob ,yymmdd10.),input(Date ,yymmdd10.),'age')) 
    else 999
end,fmtageband.))

format csv file used:

vnreddy_0-1660897836701.png

current output :

vnreddy_1-1660897992092.png

Could someone help me with the current output : how to change 05-Sep to as 05-09 and  '    999' to 999(remove trailing blanks)

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

And, although we've talked about this millions of times here, never open a text file with Excel to inspect it. Open it with a text editor (even Windows Notepad will do, but it might choke on big files) instead. Notepad++ is the perfect choice for this.

View solution in original post

9 REPLIES 9
Kurt_Bremser
Super User

Always (as in ALWAYS) post data as data steps with datalines, NEVER as pictures. NEVER.

Post text files as text in a window opened with this button:

Bildschirmfoto 2020-04-07 um 08.32.59.jpg

and post the code you used to make SAS datasets out of them.

Also post the code you used to create the format, so we can recreate it, or advise on how to change it so it works.

PaigeMiller
Diamond | Level 26

You only show us a tiny portion of your code, we don't know what you did. Show us the data as SAS Data step code. Show us the entire program. If there are errors in the LOG, show us the ENTIRE log.

 

By the way, from an earlier post of yours, you KNOW that we want data presented as SAS data step code. We shouldn't have to constantly ask for data to be properly presented. From now on, please consider this mandatory, it should be done in every future post without us having to ask.

--
Paige Miller
vnreddy
Quartz | Level 8

Hi,

 

I have used the provided expression in SAS DI studio to get the right values for Age column, but i am getting wrong output as

05-Sep instead of 05-09 and '   999' trailing blanks instead of 999.

 

Any suggestion to modify the expression which i provided would be helpful in getting the right results.

PaigeMiller
Diamond | Level 26

Please provide the information requested.

--
Paige Miller
vnreddy
Quartz | Level 8

 

Below expression is giving me trailing blanks when i have 999 values, could you please give me some suggestion/modification to avoid the trailing blanks for 999 values.

 

trim(put(case 
    when yrdif(input(Dob ,yymmdd10.),input(Date ,yymmdd10.),'age') = . then 999
    when     yrdif(input(Dob ,yymmdd10.),input(Date ,yymmdd10.),'age') >=  0 
    then floor(yrdif(input(Dob ,yymmdd10.),input(Date ,yymmdd10.),'age')) 
    else 999
end,fmtageband.))
Kurt_Bremser
Super User

@vnreddy wrote:

 

Below expression is giving me trailing blanks when i have 999 values, could you please give me some suggestion/modification to avoid the trailing blanks for 999 values.

 

trim(put(case 
    when yrdif(input(Dob ,yymmdd10.),input(Date ,yymmdd10.),'age') = . then 999
    when     yrdif(input(Dob ,yymmdd10.),input(Date ,yymmdd10.),'age') >=  0 
    then floor(yrdif(input(Dob ,yymmdd10.),input(Date ,yymmdd10.),'age')) 
    else 999
end,fmtageband.))

You already posted this, but we requested (MUCH!!!) more information. Reposting only this will not help in the least.

If you do not intend to assist us in helping you, then why are you here at all?

Kurt_Bremser
Super User

And, although we've talked about this millions of times here, never open a text file with Excel to inspect it. Open it with a text editor (even Windows Notepad will do, but it might choke on big files) instead. Notepad++ is the perfect choice for this.

Tom
Super User Tom
Super User

What is that last photograph a picture of?

Did you accidentally let Excel open a CSV file and make its own guesses about what the strings in the text file might mean?  That would explain why string likes '5-9' are being displayed as some type amorphous date that does not show the year value.

ballardw
Super User

 

Personally, looks like EXCEL display and never trust Excel to not reformat values. Never. Especially if you paste some result into Excel.

 

Example: Open a new Excel worksheet, pick a cell, type 5-9. That is a five, a dash, and a 9. What does Excel display?

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 941 views
  • 0 likes
  • 5 in conversation