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
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.
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.
Hi Paige,
Thank you so much for your kind support. Brilliant, it works perfectly. Thanks
Here is some data:
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.
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!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.