topic Re: Format Issue in SAS Programming

Format Issue

ZahidHussain — Sat, 16 Apr 2022 19:16:41 GMT

HI Folks,

I am wondering how to format this type of character data.

The data is:

data test;

input result $20. ;

datalines;

< 20
101
Target Not Detected
1.05E+3 (3.02)
322286
162
;
run;

I want to decode this value like that:

1 = viral load suppressed (<1000 copies / mL)
2 = not viral load suppressed
99 = Missing

Anybody gives me some idea that how can I do that?

Thanks

Re: Format Issue

Tom — Sat, 16 Apr 2022 19:56:00 GMT

A format is used to convert values into text. So you could think of them like a decoder ring.

So in your case it looks you want a format to DECODE 1 into 'viral load suppressed' etc.

So something like this (it makes no sense to code missing values as a valid number like 99).

proc format ;
  value viral_load 
  1='viral load suppressed (<1000 copies / mL)'
  2='not viral load suppressed'
  .='Missing'
  ;
run;

But the real problem it looks like you are having is how to ENCODE the strings you have into the appropriate codes.

What is the logic for handling those example strings?

Re: Format Issue

ZahidHussain — Sat, 16 Apr 2022 23:27:34 GMT

Thanks for your reply. Encoding is problem. The result variable is character which has numeric number as well as character. If I use input function some value would be missing . So my question is, How I do format this variable both has numeric and character value

Re: Format Issue

Tom — Sun, 17 Apr 2022 02:20:56 GMT

You posted examples of the free text field you have, but did not provide any rules (or even a sketch of the rules) for categorizing them. You did not even provide the categories you want for the example text you provided.

Keep the text as one variable. You can then convert that into one or two more variables to help you categorize them.

For example you could start by handling the text that can actually be interpreted as a number.

So if you have the dataset HAVE with the character variable RESULT.

data HAVE;
  input result $20. ;
datalines;
.
< 20
101
Target Not Detected
1.05E+3 (3.02)
322286
162
;

You could create the numeric variable RESULTN and then categorize that.

data want;
  set have;
  resultn=input(result,??32.);
  if missing(result) then category=.;
  else if missing(resultn) then category=.U;
  else if resultn < 1000 then category=1;
  else category=2;
run;

Results:

Obs    result                 resultn    category

 1                                  .        .
 2     < 20                         .        U
 3     101                        101        1
 4     Target Not Detected          .        U
 5     1.05E+3 (3.02)               .        U
 6     322286                  322286        2
 7     162                        162        1

You can then work on making the logic smarter for the .U category values. Perhaps you can also test if RESULT starts with '<' and take that in consideration when tying to assign a category. Or you could just build up a list of all of the unknown RESULTS and give them to a human to categorize. Then use that list to make a dataset or perhaps an INFORMAT that can convert some of those in the appropriate 1 or 2 category.

Re: Format Issue

ZahidHussain — Sun, 17 Apr 2022 05:34:50 GMT

Hi, Thanks for your reply. I got your point. One question this data file originally from CSV. and the one value of the variable is 1.05E+3 (3.02) which is character format. Now my question is how I convert it to numeric format and without scientific notation (E) in SAS?
Thanks

Re: Format Issue

Kurt_Bremser — Sun, 17 Apr 2022 08:02:22 GMT

Is this a comprehensive list of input values?

Re: Format Issue

ZahidHussain — Sun, 17 Apr 2022 08:39:19 GMT

Yes it is

Re: Format Issue

ZahidHussain — Sun, 17 Apr 2022 08:39:30 GMT

yes it is

Re: Format Issue

Kurt_Bremser — Sun, 17 Apr 2022 12:12:45 GMT

And how do the values 1, 2 and 99 map to the strings?

Re: Format Issue

Tom — Sun, 17 Apr 2022 15:31:16 GMT

@ZahidHussain wrote:
Hi, Thanks for your reply. I got your point. One question this data file originally from CSV. and the one value of the variable is 1.05E+3 (3.02) which is character format. Now my question is how I convert it to numeric format and without scientific notation (E) in SAS?
Thanks

And what number do you think that string means?

It does not look like a valid number because of the extra characters at the end. So "1.05E+3" is just another way of writing 1050. But the extra character make it an invalid input. What is the meaning of the "(3.02)" at the end?

Re: Format Issue

ZahidHussain — Sun, 17 Apr 2022 20:25:32 GMT

I am not pretty sure . As I know the E means 2.71. The same number is in the data set. So I am confused about this

Re: Format Issue

ZahidHussain — Mon, 18 Apr 2022 01:26:38 GMT

I think you are right, That number is invalid number , so its supposed to come missing. Thanks everybody for giving me the solution

Re: Format Issue

SASKiwi — Mon, 18 Apr 2022 03:42:50 GMT

It would be advisable to check with the provider of the data regarding the correct treatment of the various values. If the exponential value is unique, then setting it to missing sounds reasonable. However if you are getting a range of exponential values you might be missing some real data.

Re: Format Issue

ZahidHussain — Mon, 18 Apr 2022 04:24:09 GMT

I check this number in excel with trim function . It gives invalid number. Thats why I think that this is invalid input. Also this data set there is only one variable like that. Also when I run the program with best32. format it gives the missing value. Any idea of changing the format of exponential value to numeric value.
Thanks

Re: Format Issue

SASKiwi — Mon, 18 Apr 2022 04:44:18 GMT

You could try this:

data want;
  set have;
  exponential_num = input(substr(exponential_char,1,7), 7.);
run;

Re: Format Issue

Sajid01 — Mon, 18 Apr 2022 14:16:24 GMT

Hello
The understanding that E is 2.71 is incorrect. It is e that is 2.7123....
In the present context 1.05E+3 implies 1.05 x 10^+3 and this equal 1050.
From what that has been mentioned, the data [reparation step is being by passed. For example "Target Not Detected" means the component that was being analyzed is either absent or in such a small quantity that it could not be detected. In is typical in certain situations to take this as zero.

Re: Format Issue

ZahidHussain — Mon, 18 Apr 2022 15:47:07 GMT

Thanks for your reply. You cleared lot if things. But the original number is 1.05E+3(3.02) . So, at the end of this (3.02) gives invalid as I know. So, whats your suggestion. take this number as a invalid or take substr from 1 to 7 which gives the number 1050. Thanks

Re: Format Issue

Sajid01 — Mon, 18 Apr 2022 16:22:23 GMT

I do not know the line of activity or purpose of the analysis of this data. Consult with domain specialist for guidance. This is particularly because it does not fall under 1, 2 or 99, the values you are trying to encode to.

Re: Format Issue

Sajid01 — Mon, 18 Apr 2022 17:19:29 GMT

Great. If that solves your question, pleas do mark the question as resolved