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

Hi,

I am stuck on the above mentioned issue. I have a data set with a variable date only year like 8300 BCE, 1282 CE, Unknown.I want to make it numeric variable with negative values for BCE dates and positive values for CE dates. I tried to make it with SCAN function, but it comes only numeric value like 8300, 1282, unknown. My question is how I can do it according to my question.

Please find attached a part of the data:

 

Looking for your kind help

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

Only year ... if there is a text string, such as 'Unknown' or even '1282 CE', these will be read as character strings. There's no way to read them as numbers.

 

So read them as character strings, and then convert them in a data step, for example

 

data want;
    set have;
    if find(year,'bce','i')>0 then year1=input(scan(year,1),4.)*-1;
    else if find(year,'ce','i')>0 then year1=input(scan(year,1),4.);
    else year1=.;
run;

 

Of course, this depends on the formatting of your years in the data set, it may be that other coding is needed.

 

PS: I see that you have attached a data set, but I never download files from public forums. You could copy and paste 5 or 10 rows of your .csv file into your message, I would be able to use the data in that case.

--
Paige Miller

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

Only year ... if there is a text string, such as 'Unknown' or even '1282 CE', these will be read as character strings. There's no way to read them as numbers.

 

So read them as character strings, and then convert them in a data step, for example

 

data want;
    set have;
    if find(year,'bce','i')>0 then year1=input(scan(year,1),4.)*-1;
    else if find(year,'ce','i')>0 then year1=input(scan(year,1),4.);
    else year1=.;
run;

 

Of course, this depends on the formatting of your years in the data set, it may be that other coding is needed.

 

PS: I see that you have attached a data set, but I never download files from public forums. You could copy and paste 5 or 10 rows of your .csv file into your message, I would be able to use the data in that case.

--
Paige Miller
Uddin
Fluorite | Level 6

Hi Paige,

Thank you so much for your kind support. Brilliant, it works perfectly. Thanks

Here is some data:

Uddin_0-1655003740800.png

 

ballardw
Super User

One way reading a few line of text from your attachment:

data example;
   infile datalines truncover;
   input textdate $10.;
   if index(textdate,'BCE')>0 then year= (-1)* input(scan(textdate,1),4.);
   else if index(textdate,'CE')>0 then year=  input(scan(textdate,1),4.);
datalines;
8300 BCE
4040 BCE
Unknown
3600 BCE
1282 CE
104 BCE
Unknown
1538 CE
1944 CE
1302 CE
8040 BCE
2016 CE
Unknown
;

Your use of SCAN was probably correct just did not check for the existence of the string portion of "BCE" to make the values negative. INDEX is one of the functions you can use to check if a string value is part of another character value. It will return the position number of the start of the string if found or 0. SAS will treat non-zero non-missing values as true so works for the logic shown.

Note the above will leave the "Unknown" as missing values.

Uddin
Fluorite | Level 6
Hi Ballardw,
Thanks for your kind support. The code is working perfectly. Kind Regards

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1050 views
  • 0 likes
  • 3 in conversation