Thanks again for the suggestion. We have added the following to the description: SAS® 9.4 Formats and Informats: Reference - go.documentation.sas.com/?docsetId=leforinforref&docsetTarget=titlepage.htm&docsetVersion=9.4&locale=en
To convert a spelled out whole number to the actual number, I came up with this, which creates an informat called numf data _null_; call execute('proc format; invalue numf "none"=0'); do i=0 to 10000; numtxt=put(i,words100.); call execute(quote(strip(numtxt))||'='||cats(i)); end; call execute(';run;'); run;
Communities is a great place to engage in a discussion around topics like this! These communities are a service hosted by SAS to facilitate the exchange of technical information pertaining to SAS products, services and best practices. For assistance with how to post a great question or get a discussion going, click here: 2.sas.com/6057TljPc
Hi, there! If you search in the SAS Community Forums, you'll find this example 2.sas.com/6054rQxP8 of using the WORDS. format to write a numeric value as a word. You can read the documentation here: 2.sas.com/6057rQxP1 . Both the documentation and the forum posting have examples. Happy learning!
My date value went from 2024/01/10 to 23385 when I did the following. I'm also noticing the date fields are CHAR with format yymmdd10. Why did SAS render a integer SAS date but not convert data type to Numeric? DATA testsql.HospitalAdmissions2; set testsql.hospitaladmissions; AdmissionDate = input(AdmissionDate ,yymmdd10.); DischargeDate = input(DischargeDate ,yymmdd10.); DateofBirth = input(DateofBirth ,yymmdd10.); run;
After playing with it some more, I'm guessing it's how the original data set was created. PROC SQL was used to INSERT 'AdmissionDate' etc into table hospitaladmissions. I'm guessing it's the non visible single quotes from the original dataset that throwing it off.
Tejashwini, Thanks for the suggestion about doing a video on PROC TRANSPOSE. We do cover PROC TRANSPOSE in our Programming 2 class. You can read the description about Programming 2 here: 2.sas.com/6052Hqyen . The documentation also has some good examples, here: 2.sas.com/6054Hqyek This Tech Support note shows an interesting use of PROC TRANSPOSE with multiple BY variables: 2.sas.com/6055HqyeZ . Here's a good user group paper on the use of TRANSPOSE: 2.sas.com/6056Hqyew . To find more user group papers on the use of PROC TRANSPOSE, you can use this search string 2.sas.com/6057Hqyeb to search the LexJansen site, which is an archive of over 30,000 user group papers.
A full discussion of using PROC SQL is outside the scope of this video since the video shows the conversion of variables from character to numeric and vice versa. We cover PROC SQL in the Programming 1 class and in the SQL 1 class. If you did a simple order by clause with salary descending, then you'd be able to see the salaries in descending order. The second one on the list would be the one you wanted. Or, alternate approaches would be to use PROC SORT, PROC UNIVARIATE or even PROC RANK. We cover the use of PROC SQL and PROC UNIVARIATE in our Programming 1 class. You can read about PROC RANK in the documentation if you are interested in an alternate approach. For general usage questions, you might want to consider posting in the SAS Community Forums for SAS Procedures, which includes PROC SQL: 2.sas.com/60511V3Z5 We hope this information helps!
Hi Anna, I have a situation where I have to convert the character value as "5554219.00871785" to Numeric variable. When am trying to convert using "input(char variablename,best.)", the output in the numeric variable is - "5554219.0087". Is there a way to get the same values as I have in the character variable while converting to Numeric ? Appreciate your response on this. Thanks
Hi Shankar. Yes, SAS 9.2 will work for learning purposes however most of our videos reference our newer 9.4 version. You can access OnDemand for Academics: Studio for learning purposes for free. Click here to learn more: 2.sas.com/6050G7DIp
In this video, the name of the original data set is LIB.RACE and the instructor in the video is creating WORK.RACE_NEW. RACE_NEW is the table name or dataset name. It is not the name of the column. In the video, the instructor is creating a new numeric variable or column called Date from the original variable or column called Race_Date. The program that you see at time stamp 2:25 shows that RACE_NEW is the new version of the original data, with the new Date column added. If you notice at time stamp 2:30, the order of the columns in the NEW table is the SAME as the order of the columns in the original table. The only difference is that the new Date column appears in the table after all the other variables from the original data table. So, as you see, the original column order is maintained in the new table. If you are asking how you could turn RACE_DATE from character to numeric without creating a new variable, that is a more advanced topic that would require more space to answer than we have available. You would need to use the RENAME= option with the KEEP= option or the KEEP statement, and we show these techniques in our free Programming 1 class. We hope this helps!
Hello Anna, In SAS is there any way to clean up the Excel file. Like for example we have one Excel file data with lot of headings ...actual data start with cell F1 (this will vary file to file ) instead of cell A1.. Kindle advise
The simplest way do this in SAS is to define a range in the excel spreadsheet you want to read. Here is a great blog post on how to do that: 2.sas.com/6054EaC3e
Shreyas, thanks for your inquiry! Use the INPUT function with an informat to tell SAS how to interpret the value and convert to a number. See this SAS Note for more info: 2.sas.com/6057Gmpsx
Hi Anna!! Can you please confirm whether we can convert words to numeric through function? E.g. Data _null_; Value=255; Put value word22.; Run; Output Two hundred fifty-five Similarly can we change Two hundred fifty-five to 255 ? Please guide.
It is possible to convert character strings like '255' to a numeric variable using the INPUT function. However, you would have to write your own program or INFORMAT to convert a string like "two hundred fifty-five" to a numeric value. The WORDS format that you showed is used to display or convert a numeric variable as the words for the number, as you might write them on a check. Typically you would use the WORDS format in a FORMAT statement or a PUT function. Ratnakar, there is NOT an equivalent WORDS informat to convert from a word string back to a number. Please refer to this Tech Support note for examples of using the INPUT function to convert character values to numeric values and character date values to SAS date values: 2.sas.com/6053Ehdkl . You could write your own informat to use for reading character data. You'd need to use PROC FORMAT with an INVALUE statement to do this. If you need help with a specific variable conversion problem or program, then we recommend that you post your question in the SAS Community Forum for SAS Programming, here: 2.sas.com/6055EhdkW or else, open a track with Tech Support by filling out this form, here: 2.sas.com/6056Ehdko
SAS Users Thanks for your help!! I have tried input and proc format. I was curious whether we have any format or not. Thank you so much and link is really useful.
In this video, the instructor shows the use of the INPUT function to convert the character value for date into a SAS date value. A SAS date value is a number that represents the number of days since Jan 1, 1960. If you have a variable that represents time, then it probably has a : (colon) in the value. If this is the case, then the variable is probably a character string with a colon and you would use the same INPUT function, but instead of a date informat, you would need to use a time informat. You can look in the INFORMAT documentation by category, here: 2.sas.com/60541hcyp to see a list of all the possible date and date/time informats that you can use. Or, if you have 3 numeric variables, such as a number for hours, another number for minutes and another number for seconds, then you can use a different function, like the HMS function which takes an hour, minute and second value and returns a numeric value that represents a SAS time. Here's the documentation for the HMS function: 2.sas.com/60551hcyV . If you have a specific usage issue with your data, you might want to post a question in the SAS Communities Forum or search there for similar questions. Otherwise, you can open a track with Tech Support on this question.
Hi Anil, you can use the RANK function to convert a character to a code, then use math to base at 0. Here is an example program: data test; infile datalines; length have $ 12 val1-val3 8; input have; val1 = rank(scan(have,1,',')) - rank('a'); val2 = rank(scan(have,2,',')) - rank('a'); val3 = rank(scan(have,3,',')) - rank('a'); datalines; a,b,c ; run;
@@gandytim7359 Is this a zip code? Usually we recommend storing ZIP codes as character as a postal code isn't something you do math with, and some have leading zeroes that won't appear in default formats. However, if using a numeric, apply the Z5. format to preserve the leading 0.
We don't see this line of code in this video, Lakshmana. Will you please let us know where you found it and any more context to help us understand what you'd like to know about it?
We're sorry you feel that way. We're always up for user feedback, feel free to share via SAS Idea Exchange: 2.sas.com/6057JNU67. Additional info on customer feedback, here: 2.sas.com/6059JNU6h
Hi Mam I need a suggestion. I have a variable x of type char in my data set. one of its row is blank but while calculating its length I am getting length 1. Sir is there a way to find what is the value hidden at that blank row.
Ajay, The concept of a true missing is always a bit mystifying, especially for character values. In SAS, a missing character value is represented by a single blank enclosed in quotes (''). But because of the requirements for storage, the length for a missing value is always 1 because the missing space although you can't see it takes up one byte of storage for the length. So then the question becomes, how can I know the difference between the LENGTH function returning a length of 1 for a true missing value compared to the length function returning 1 because there is only one value in the field and it's NOT missing? The answer is don't use the LENGTH function to determine whether a field is missing or not, use the MISSING function instead. If the character value is truly missing('') the MISSING function returns a value of 1, if the character value is not missing the MISSING function returns a value of 0. We can send you a quick code example by email. If you feel that would be helpful, please email us at SAS_Cares@sas.com and reference Social Case # 179892
Please review the INPUT function starting at 1:35 in the video. Anna explains that you must use an appropriate informat, or reading instruction, to convert your variable. In your example, you have 123456789123456, or a 15-digit number. You must use the 15. informat in your INPUT function to read your variable. data convert; var1='123456789123456'; var2=input(var1, 15.); run; proc contents data=convert; run; You will see in the PROC CONTENTS that var1 is character and var2 is numeric. If you have any questions, please send email to curriculumconsulting@sas.com .
Hi anna this is Vishnu your negotiation is pretty good.👌
It was really helpful and finally I understood what was the Z3. Format. Thank you
Glad it helped! Thanks for sharing your feedback!
Great video!
I think it would be helpful if you could put a link to the formats and informats that are available somewhere in the description.
Thanks!
Hi Ed! Thanks for your feedback! We're looking into this for you!
Thanks again for the suggestion. We have added the following to the description:
SAS® 9.4 Formats and Informats: Reference - go.documentation.sas.com/?docsetId=leforinforref&docsetTarget=titlepage.htm&docsetVersion=9.4&locale=en
thank you so much. Saved hours of my life.
Glad it helped!
Thank you "ANNA" your video supported me a lot💝.
That is awesome! We are glad you found it helpful! 👍
you are a LIFE SAVER
Fantastic, we're so glad you found the content useful!
To convert a spelled out whole number to the actual number, I came up with this, which creates an informat called numf
data _null_;
call execute('proc format; invalue numf "none"=0');
do i=0 to 10000;
numtxt=put(i,words100.);
call execute(quote(strip(numtxt))||'='||cats(i));
end;
call execute(';run;');
run;
Communities is a great place to engage in a discussion around topics like this! These communities are a service hosted by SAS to facilitate the exchange of technical information pertaining to SAS products, services and best practices. For assistance with how to post a great question or get a discussion going, click here:
2.sas.com/6057TljPc
Thanks for your video ,l known more on behavior and enterprises problem to solution.
Thank you for sharing your knowledge!
Thank YOU for checking it out! We love hearing feedback so appreciate your sharing. Have a great day!
Hello Anna,
Thanks for the video. I wonder if you have a channel or website where we can find your own tutorials only?
Thank you!
Stay safe!
Great explanation
Thanks for the positive feedback!
was wondering if there is an informat one can use to convert numbers spelled out in words, like 'one', 'two', 'three', to 1, 2, 3
Hi, there! If you search in the SAS Community Forums, you'll find this example 2.sas.com/6054rQxP8 of using the WORDS. format to write a numeric value as a word. You can read the documentation here:
2.sas.com/6057rQxP1 .
Both the documentation and the forum posting have examples. Happy learning!
@@SASUsers thanks. need to go the other way though
My date value went from 2024/01/10 to 23385 when I did the following. I'm also noticing the date fields are CHAR with format yymmdd10. Why did SAS render a integer SAS date but not convert data type to Numeric?
DATA testsql.HospitalAdmissions2;
set testsql.hospitaladmissions;
AdmissionDate = input(AdmissionDate ,yymmdd10.);
DischargeDate = input(DischargeDate ,yymmdd10.);
DateofBirth = input(DateofBirth ,yymmdd10.);
run;
After playing with it some more, I'm guessing it's how the original data set was created. PROC SQL was used to INSERT
'AdmissionDate' etc into table hospitaladmissions. I'm guessing it's the non visible single quotes from the original dataset that throwing it off.
Thanks for the video, it was v helpful. Can u pls talk about proc transpose too.
Tejashwini, we are checking on this for you!
Tejashwini, Thanks for the suggestion about doing a video on PROC TRANSPOSE. We do cover PROC TRANSPOSE in our Programming 2 class. You can read the description about Programming 2 here: 2.sas.com/6052Hqyen .
The documentation also has some good examples, here: 2.sas.com/6054Hqyek This Tech Support note shows an interesting use of PROC TRANSPOSE with multiple BY variables: 2.sas.com/6055HqyeZ .
Here's a good user group paper on the use of TRANSPOSE: 2.sas.com/6056Hqyew . To find more user group papers on the use of PROC TRANSPOSE, you can use this search string 2.sas.com/6057Hqyeb to search the LexJansen site, which is an archive of over 30,000 user group papers.
@@SASUsers Thank you so much.
Very nice.... Helpfull video...
Upload proc sql video..
Q.1) how to get second highest value(Salary) in proc sql.?
Thanks for your question, we're currently checking on this for you!
A full discussion of using PROC SQL is outside the scope of this video since the video shows the conversion of variables from character to numeric and vice versa.
We cover PROC SQL in the Programming 1 class and in the SQL 1 class. If you did a simple order by clause with salary descending, then you'd be able to see the salaries in descending order. The second one on the list would be the one you wanted. Or, alternate approaches would be to use PROC SORT, PROC UNIVARIATE or even PROC RANK. We cover the use of PROC SQL and PROC UNIVARIATE in our Programming 1 class. You can read about PROC RANK in the documentation if you are interested in an alternate approach.
For general usage questions, you might want to consider posting in the SAS Community Forums for SAS Procedures, which includes PROC SQL: 2.sas.com/60511V3Z5 We hope this information helps!
@@SASUsers thaank you so much for reply... 😊
Absolutely!
Hi Anna, I have a situation where I have to convert the character value as "5554219.00871785" to Numeric variable. When am trying to convert using "input(char variablename,best.)", the output in the numeric variable is - "5554219.0087". Is there a way to get the same values as I have in the character variable while converting to Numeric ? Appreciate your response on this. Thanks
Thanks for the question! We are looking into this for you.
Instead of "best." try an informat that specifies the precision:
data a;
x = input("5554219.00871785",16.8);
format x 16.8;
run;
Thanks Anna for this video, I am using sas 9.2 is it beneficial to use for learning purpose?
Hi Shankar. Yes, SAS 9.2 will work for learning purposes however most of our videos reference our newer 9.4 version. You can access OnDemand for Academics: Studio for learning purposes for free. Click here to learn more: 2.sas.com/6050G7DIp
how do I replace the data in the current column with race_new while *maintaining * the original column order?
In this video, the name of the original data set is LIB.RACE and the instructor in the video is creating WORK.RACE_NEW. RACE_NEW is the table name or dataset name. It is not the name of the column. In the video, the instructor is creating a new numeric variable or column called Date from the original variable or column called Race_Date. The program that you see at time stamp 2:25 shows that RACE_NEW is the new version of the original data, with the new Date column added. If you notice at time stamp 2:30, the order of the columns in the NEW table is the SAME as the order of the columns in the original table. The only difference is that the new Date column appears in the table after all the other variables from the original data table. So, as you see, the original column order is maintained in the new table. If you are asking how you could turn RACE_DATE from character to numeric without creating a new variable, that is a more advanced topic that would require more space to answer than we have available. You would need to use the RENAME= option with the KEEP= option or the KEEP statement, and we show these techniques in our free Programming 1 class.
We hope this helps!
Hello Anna,
In SAS is there any way to clean up the Excel file. Like for example we have one Excel file data with lot of headings ...actual data start with cell F1 (this will vary file to file ) instead of cell A1..
Kindle advise
Sivaram, thanks for your question! We are researching this for you and post a reply shortly!
In Tableau we have one similar option called "data interpreter " ..looking for similar thing in SAS
The simplest way do this in SAS is to define a range in the excel spreadsheet you want to read. Here is a great blog post on how to do that: 2.sas.com/6054EaC3e
Hi Ana. Like you converted date into numeric, how to convert numbers which are in character format into numeric? Pls help.
Shreyas, thanks for your inquiry! Use the INPUT function with an informat to tell SAS how to interpret the value and convert to a number. See this SAS Note for more info: 2.sas.com/6057Gmpsx
@@SASUsers Thank you :)
You are most welcome! Glad we could help!
Hi Anna!! Can you please confirm whether we can convert words to numeric through function?
E.g.
Data _null_;
Value=255;
Put value word22.;
Run;
Output
Two hundred fifty-five
Similarly can we change Two hundred fifty-five to 255 ?
Please guide.
Ratnaka, thank you for your inquiry! We are checking into this for you!
SAS Users Thank you!! eagerly waiting for your response !!
It is possible to convert character strings like '255' to a numeric variable using the INPUT function. However, you would have to write your own program or INFORMAT to convert a string like "two hundred fifty-five" to a numeric value. The WORDS format that you showed is used to display or convert a numeric variable as the words for the number, as you might write them on a check. Typically you would use the WORDS format in a FORMAT statement or a PUT function.
Ratnakar, there is NOT an equivalent WORDS informat to convert from a word string back to a number. Please refer to this Tech Support note for examples of using the INPUT function to convert character values to numeric values and character date values to SAS date values: 2.sas.com/6053Ehdkl . You could write your own informat to use for reading character data. You'd need to use PROC FORMAT with an INVALUE statement to do this.
If you need help with a specific variable conversion problem or program, then we recommend that you post your question in the SAS Community Forum for SAS Programming, here: 2.sas.com/6055EhdkW or else, open a track with Tech Support by filling out this form, here: 2.sas.com/6056Ehdko
SAS Users Thanks for your help!! I have tried input and proc format. I was curious whether we have any format or not. Thank you so much and link is really useful.
Hey, Thank you for your video. I need help with time to numeric problem. How can I convert time (hours: minute: second) to numeric?
Thank you for you inquiry! We are looking into this for you!
In this video, the instructor shows the use of the INPUT function to convert the character value for date into a SAS date value. A SAS date value is a number that represents the number of days since Jan 1, 1960.
If you have a variable that represents time, then it probably has a : (colon) in the value. If this is the case, then the variable is probably a character string with a colon and you would use the same INPUT function, but instead of a date informat, you would need to use a time informat. You can look in the INFORMAT documentation by category, here: 2.sas.com/60541hcyp to see a list of all the possible date and date/time informats that you can use. Or, if you have 3 numeric variables, such as a number for hours, another number for minutes and another number for seconds, then you can use a different function, like the HMS function which takes an hour, minute and second value and returns a numeric value that represents a SAS time. Here's the documentation for the HMS function: 2.sas.com/60551hcyV . If you have a specific usage issue with your data, you might want to post a question in the SAS Communities Forum or search there for similar questions. Otherwise, you can open a track with Tech Support on this question.
How do I convert If I have 3 Character values in one column as a,b,c to 0,1,2 ?
Hi Anil, you can use the RANK function to convert a character to a code, then use math to base at 0. Here is an example program:
data test;
infile datalines;
length have $ 12 val1-val3 8;
input have;
val1 = rank(scan(have,1,',')) - rank('a');
val2 = rank(scan(have,2,',')) - rank('a');
val3 = rank(scan(have,3,',')) - rank('a');
datalines;
a,b,c
;
run;
Thanks
@@chrishemedinger1382 how do I put numbers 94303 into numeric
@@gandytim7359 Is this a zip code? Usually we recommend storing ZIP codes as character as a postal code isn't something you do math with, and some have leading zeroes that won't appear in default formats. However, if using a numeric, apply the Z5. format to preserve the leading 0.
derive_column = PUT(column_name, $18.)
what does this statement do?
Thanks for your question, Lakshmana! We're checking and will get back to you soon!
We don't see this line of code in this video, Lakshmana. Will you please let us know where you found it and any more context to help us understand what you'd like to know about it?
💝🤝😊
A bit too fast . But still helpful.
Kris, glad to hear you found it helpful!
I love "never rely on SAS". Sooooooooooo true. SAS blows. Unintuitive and tedious. It's no wonder Python is more popular.
We're sorry you feel that way. We're always up for user feedback, feel free to share via SAS Idea Exchange: 2.sas.com/6057JNU67. Additional info on customer feedback, here: 2.sas.com/6059JNU6h
Hi Mam I need a suggestion.
I have a variable x of type char in my data set.
one of its row is blank but while calculating its length I am getting length 1.
Sir is there a way to find what is the value hidden at that blank row.
Thank you for your inquiry! We are checking on this for you!
Ajay, The concept of a true missing is always a bit mystifying, especially for character values. In SAS, a missing character value is represented by a single blank enclosed in quotes (''). But because of the requirements for storage, the length for a missing value is always 1 because the missing space although you can't see it takes up one byte of storage for the length.
So then the question becomes, how can I know the difference between the LENGTH function returning a length of 1 for a true missing value compared to the length function returning 1 because there is only one value in the field and it's NOT missing? The answer is don't use the LENGTH function to determine whether a field is missing or not, use the MISSING function instead. If the character value is truly missing('') the MISSING function returns a value of 1, if the character value is not missing the MISSING function returns a value of 0. We can send you a quick code example by email. If you feel that would be helpful, please email us at SAS_Cares@sas.com and reference Social Case # 179892
SAS vs R vs python?
Hi Anna, how do I convert the following charater string to numeric?
'123456789123456'
We're checking on this for you and will get back to you soon!
Please review the INPUT function starting at 1:35 in the video. Anna explains that you must use an appropriate informat, or reading instruction, to convert your variable. In your example, you have 123456789123456, or a 15-digit number. You must use the 15. informat in your INPUT function to read your variable.
data convert;
var1='123456789123456';
var2=input(var1, 15.);
run;
proc contents data=convert;
run;
You will see in the PROC CONTENTS that var1 is character and var2 is numeric. If you have any questions, please send email to curriculumconsulting@sas.com .