topic Re: Converting char column to numeric format in SAS Programming

Converting char column to numeric format

Pandu2 — Fri, 05 Nov 2021 12:31:07 GMT

Hi,
I've a temp dataset named "vipl" which has 5 columns named pr,qt,des,cod,addr in a library which is of type char but needs to convert it to numeric. I tried using input func. Below is the code.

Data service.ipl;
Set service.vipl;(rename(pr=old qt=older));
pr=input(old,10.);
qt=input(older,10.);
drop old older;
run;
The above code gives a dataset by removing the existing columns i.e des,cod,addr and just giving pr,qt . So, can anyone help me in solving the issue. Thanks in advance.

Re: Converting char column to numeric format

PeterClemmensen — Fri, 05 Nov 2021 12:37:40 GMT

You want the names to be the same, but the type to change from character to numeric, correct?

Re: Converting char column to numeric format

Pandu2 — Fri, 05 Nov 2021 12:41:41 GMT

Yes, you're right

Re: Converting char column to numeric format

PeterClemmensen — Fri, 05 Nov 2021 12:58:02 GMT

See if you can use this as a template

data have;
   pr   = "1";
   qt   = "2";
   des  = "3";
   cod  = "4";
   addr = "5";
run;

data want;
   set have(rename = (pr = _pr qt = _qt des = _des cod = _cod addr = _addr));
   pr   = input(_pr, 8.);
   qt   = input(_qt, 8.);
   des  = input(_des, 8.);
   cod  = input(_cod, 8.);
   addr = input(_addr, 8.);
   drop _:;
run;

Re: Converting char column to numeric format

Pandu2 — Fri, 05 Nov 2021 13:02:40 GMT

Thanks for the help but, my issue is I just wanted to change the format for 2 columns pr,qt from char to numeric and the rest should be same which is char. And I did that but output dataset has only pr,qt didn't have the rest. Please refer my code.
Thanks.

Re: Converting char column to numeric format

Pandu2 — Fri, 05 Nov 2021 13:08:04 GMT

FYI,
The columns has char data which are numeric but the type of the column variable is char.

Re: Converting char column to numeric format

PeterClemmensen — Fri, 05 Nov 2021 13:11:18 GMT

Like this?

data have;
   pr   = "1";
   qt   = "2";
   des  = "3";
   cod  = "4";
   addr = "5";
run;

data want;
   set have(rename = (pr = _pr qt = _qt));
   pr   = input(_pr, 8.);
   qt   = input(_qt, 8.);
   drop _:;
run;

Re: Converting char column to numeric format

Pandu2 — Fri, 05 Nov 2021 13:15:52 GMT

Yes, but that want output dataset hasn't had the rest of the columns.

Re: Converting char column to numeric format

PeterClemmensen — Fri, 05 Nov 2021 13:17:33 GMT

Yes it does?

Re: Converting char column to numeric format

Pandu2 — Fri, 05 Nov 2021 13:22:12 GMT

What I exactly need is among the 5 columns which has huge records. Two of them must be converted char to numeric and the resultant dataset should have format changed columns as well as rest of the columns(un formatted columns).

Re: Converting char column to numeric format

ballardw — Fri, 05 Nov 2021 15:50:51 GMT

@Pandu2 wrote:
Hi,
I've a temp dataset named "vipl" which has 5 columns named pr,qt,des,cod,addr in a library which is of type char but needs to convert it to numeric. I tried using input func. Below is the code.

Data service.ipl;
Set service.vipl;(rename(pr=old qt=older));
pr=input(old,10.);
qt=input(older,10.);
drop old older;
run;
The above code gives a dataset by removing the existing columns i.e des,cod,addr and just giving pr,qt . So, can anyone help me in solving the issue. Thanks in advance.

Actually no it doesn't. The semicolon after service.vipl will make the subsequent (rename ) invalid syntax.

Show the code from your LOG with all the notes and messages. Copy the text from the log. On the forum open a text box using the </> icon above the message window and paste the text.

The text box is important because some diagnostics messages from SAS insert characters and the main message windows will reformat text making them less useful. The Log text is to see the actual code used and notes about the input and output data sets.

I also don't think you have completely described your problem. If you have random variables "disappear" that are not referenced in code shown then most likely a previous step corrupted data somewhere.

Re: Converting char column to numeric format

Pandu2 — Fri, 05 Nov 2021 16:00:57 GMT

Thanks for letting me know it was my bad unfortunately I kept semicolon over there but there was no semicolon on my code. My issue is I have 5 columns in a dataset of those 2 needs to be converted to numeric. I used input func and also you can refer my code. Eventually the resultant dataset has only format changed columns rather having all the columns in the output dataset. I need some assistance in having include the rest of the columns as well.

Re: Converting char column to numeric format

ballardw — Fri, 05 Nov 2021 17:54:28 GMT

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

Of your starting data set.

Then we can write code that uses your actual data values.

Note that the code will change the order of COLUMNS that variables appear in. If your data set has enough columns or use a small window to look at a data set you may just not be scrolling over to the right far enough to see the values you expect.

Re: Converting char column to numeric format

Tom — Fri, 05 Nov 2021 22:15:11 GMT

Your code should work (assuming you fix the small typo and the names you used for the temporary names do not conflict with the names of other variables).

You can make it a little more robust be making sure to remove any leading spaces that might be in the character variables. Also the INPUT() function does not care if the informat width is longer than the character string be read so go ahead and just use the maximum 32 bytes that the numeric informat can handle.

data service.ipl;
  set service.vipl(rename(pr=old qt=older));
  pr=input(left(old),32.);
  qt=input(left(older),32.); 
  drop old older;
run;

If you are seeing variables "disappear" then something else is happening.

You ran different code than you posted.

You looked at a different dataset than your code created.

The code did not run and you are looking an existing dataset that was not replaced because of whatever issue caused the code to not run.

The SAS log will have the information that will let you figure out what happened.

Re: Converting char column to numeric format

Kurt_Bremser — Fri, 05 Nov 2021 22:28:38 GMT

You were asked to post your log. DO IT.

See Maxim 2, the log is the most important start point for diagnostics.