topic Re: how to convert multiple variables into numeric ? in SAS Programming

how to convert multiple variables into numeric ?

srikanthyadav44 — Mon, 04 Oct 2021 05:03:18 GMT

dear all,

my dataset has company_name( as character) and 84 other variables.

out of the 84 variables some variables are in character form and some variables are in numeric form.

I have to convert all the variables except, company_name into numeric in one attempt

can anybody suggest the SAS code to do this.

thanks in advance

the format of my dataset is

company_name	var1	var2	………………………………	var84
Talwalkars Better Value Fitness Ltd	50.06	42.5			37.9
Tarapur Transformers Ltd	52.06	43.75			39.65
GB Global Ltd	51.06	43.5			38.9
Jaypee Infratech Ltd	53.06	44.75			40.65
SJVN Ltd	52.06	44.5			39.9
Rainbow Papers Ltd	54.06	45.75			41.65
Sundaram Multi Pap Ltd	53.06	45.5			40.9
Pioneer Distilleries Ltd	55.06	46.75			42.65
Technofab Engineering Ltd	54.06	46.5			41.9
Denso India Pvt Ltd	56.06	47.75			43.65
Manappuram Finance Ltd	55.06	47.5			42.9
Lakshmi Finance and Industrial Corp Ltd	57.06	48.75			44.65
Hindustan Media Ventures Ltd	56.06	48.5			43.9
Tilaknagar Industries Ltd	58.06	49.75			45.65
Indian Metals and Ferro Alloys Ltd	57.06	49.5			44.9
Bliss GVS Pharma Ltd	59.06	50.75			46.65
Emami Realty Ltd	58.06	50.5			45.9

Re: how to convert multiple variables into numeric ?

ChrisNZ — Mon, 04 Oct 2021 06:26:20 GMT

Like this?

data HAVE;
  COMPANY_NAME='A';	VAR1='2';	VAR2='2.2';
run;	

proc contents data=HAVE out=CONT noprint;
run;

proc sql noprint;
  select cat('_',NAME,'=input(',NAME,',best.);drop ',NAME,'; rename _',NAME,'=',NAME) 
     into :code separated by ';'
  from CONT
  where NAME like 'VAR%' and TYPE=1;
quit;       

data WANT;  
  set HAVE;
  &code;
run;

Note that the where clause can be simplified. Keep the selection criteria that you prefer if you want.

Re: how to convert multiple variables into numeric ?

ballardw — Mon, 04 Oct 2021 06:53:10 GMT

Typically if you have variables that should be numeric or not then how you read the data into a SAS data set is the actual issue. You can fix this set but if the process stays the same then the next file needs to be "fixed" and not unlikely with a different set of problem variables.

Read the data as needed the first time and you don't have to chase down "fixes". The proper data step to read the file would likely be a best practice "single step".

One big issue is how those character values look. If there are things like currency symbols or commas then we need to know by variable so the appropriate informat can be used for the conversion. If you have other non-numeric values such as NULL, NA, MISSING, NOT RECORDED, or just plain random letters, then we need to know what values you expect to appear in the resulting data for each different not-obviously a number text value.

We had someone a couple of weeks ago that expected to turn 860-ABC-44 or similar into numbers without telling us what the numeric value would be or the rules for deriving it.

Re: how to convert multiple variables into numeric ?

andreas_lds — Mon, 04 Oct 2021 06:58:12 GMT

By fixing the obviously broken process reading the data.

Re: how to convert multiple variables into numeric ?

srikanthyadav44 — Tue, 05 Oct 2021 04:21:58 GMT

dear ChrisNZ

thanks for responding to my query

the code suggested by you is not working.

the variables along with their type is as follows

	Num	8	COMMA15.2		ESG2000
ESG2001	Num	8	COMMA15.2		ESG2001
ESG2002	Num	8	COMMA15.2		ESG2002
ESG2003	Num	8	COMMA15.2		ESG2003
ESG2004	Num	8	COMMA15.2		ESG2004
ESG2005	Num	8	COMMA15.2		ESG2005
ESG2006	Num	8	COMMA15.2		ESG2006
ESG2007	Num	8	COMMA15.2		ESG2007
ESG2008	Num	8	COMMA15.2		ESG2008
ESG2009	Num	8	COMMA15.2		ESG2009
ESG2010	Num	8	COMMA15.2		ESG2010
ESG2011	Num	8	COMMA15.2		ESG2011
ESG2012	Num	8	COMMA15.2		ESG2012
ESG2013	Num	8	COMMA15.2		ESG2013
ESG2014	Num	8	COMMA15.2		ESG2014
ESG2015	Char	1	$1.	$1.	ESG2015
ESG2016	Char	1	$1.	$1.	ESG2016
ESG2017	Char	1	$1.	$1.	ESG2017
ESG2018	Char	1	$1.	$1.	ESG2018
ESG2019	Char	1	$1.	$1.	ESG2019
ESG2020	Char	1	$1.	$1.	ESG2020
Environment2000	Num	8	COMMA15.2		Environment2000
Environment2001	Num	8	COMMA15.2		Environment2001
Environment2002	Num	8	COMMA15.2		Environment2002
Environment2003	Num	8	COMMA15.2		Environment2003
Environment2004	Num	8	COMMA15.2		Environment2004
Environment2005	Num	8	COMMA15.2		Environment2005
Environment2006	Num	8	COMMA15.2		Environment2006
Environment2007	Num	8	COMMA15.2		Environment2007
Environment2008	Num	8	COMMA15.2		Environment2008
Environment2009	Num	8	COMMA15.2		Environment2009
Environment2010	Num	8	COMMA15.2		Environment2010
Environment2011	Num	8	COMMA15.2		Environment2011
Environment2012	Num	8	COMMA15.2		Environment2012
Environment2013	Num	8	COMMA15.2		Environment2013
Environment2014	Num	8	COMMA15.2		Environment2014
Environment2015	Char	1	$1.	$1.	Environment2015
Environment2016	Char	1	$1.	$1.	Environment2016
Environment2017	Char	1	$1.	$1.	Environment2017
Environment2018	Char	1	$1.	$1.	Environment2018
Environment2019	Char	1	$1.	$1.	Environment2019
Environment2020	Char	1	$1.	$1.	Environment2020
Governance2000	Num	8	COMMA15.2		Governance2000
Governance2001	Num	8	COMMA15.2		Governance2001
Governance2002	Num	8	COMMA15.2		Governance2002
Governance2003	Num	8	COMMA15.2		Governance2003
Governance2004	Num	8	COMMA15.2		Governance2004
Governance2005	Num	8	COMMA15.2		Governance2005
Governance2006	Num	8	COMMA15.2		Governance2006
Governance2007	Num	8	COMMA15.2		Governance2007
Governance2008	Num	8	COMMA15.2		Governance2008
Governance2009	Num	8	COMMA15.2		Governance2009
Governance2010	Num	8	COMMA15.2		Governance2010
Governance2011	Num	8	COMMA15.2		Governance2011
Governance2012	Num	8	COMMA15.2		Governance2012
Governance2013	Num	8	COMMA15.2		Governance2013
Governance2014	Num	8	COMMA15.2		Governance2014
Governance2015	Char	1	$1.	$1.	Governance2015
Governance2016	Char	1	$1.	$1.	Governance2016
Governance2017	Char	1	$1.	$1.	Governance2017
Governance2018	Char	1	$1.	$1.	Governance2018
Governance2019	Char	1	$1.	$1.	Governance2019
Governance2020	Char	1	$1.	$1.	Governance2020
Social2000	Num	8	COMMA15.2		Social2000
Social2001	Num	8	COMMA15.2		Social2001
Social2002	Num	8	COMMA15.2		Social2002
Social2003	Num	8	COMMA15.2		Social2003
Social2004	Num	8	COMMA15.2		Social2004
Social2005	Num	8	COMMA15.2		Social2005
Social2006	Num	8	COMMA15.2		Social2006
Social2007	Num	8	COMMA15.2		Social2007
Social2008	Num	8	COMMA15.2		Social2008
Social2009	Num	8	COMMA15.2		Social2009
Social2010	Num	8	COMMA15.2		Social2010
Social2011	Num	8	COMMA15.2		Social2011
Social2012	Num	8	COMMA15.2		Social2012
Social2013	Num	8	COMMA15.2		Social2013
Social2014	Num	8	COMMA15.2		Social2014
Social2015	Char	1	$1.	$1.	Social2015
Social2016	Char	1	$1.	$1.	Social2016
Social2017	Char	1	$1.	$1.	Social2017
Social2018	Char	1	$1.	$1.	Social2018
Social2019	Char	1	$1.	$1.	Social2019
Social2020	Char	1	$1.	$1.	Social2020
company_name	Char	107	$107.	$107.	company_name

i need all the variables, except company_name in numeric format

please suggest me an appropriate SAS code

thanks in advance

Re: how to convert multiple variables into numeric ?

ChrisNZ — Tue, 05 Oct 2021 04:41:25 GMT

Have you tried understanding the code to adapt it?

The variables you show now have a different name so you must use these names.

Also, I agree that the process should be fixed so this kind of situation does not happen.

Closer to your new data:

data HAVE;
  COMPANY_NAME='A';	VAR1='2';	VAR2='2.2';
run;	

proc contents data=HAVE out=CONT noprint;
run;

proc sql noprint;
  select cat('_',NAME,'=input(',NAME,',best.);drop ',NAME,'; rename _',NAME,'=',NAME) 
     into :code separated by ';'
  from CONT
  where NAME ne 'COMPANY_NAME' and TYPE=2;
quit;       

data WANT;  
  set HAVE;
  &code;
run;

Re: how to convert multiple variables into numeric ?

Kurt_Bremser — Tue, 05 Oct 2021 07:55:56 GMT

How does your data arrive in SAS?

- from direct access to a database

- read from a website

- import from files (if that is the case, which file format)

- other (please describe)

Re: how to convert multiple variables into numeric ?

srikanthyadav44 — Tue, 05 Oct 2021 11:27:02 GMT

import from excel file

Re: how to convert multiple variables into numeric ?

srikanthyadav44 — Tue, 05 Oct 2021 11:36:15 GMT

i have changed the variable names as

VAR1, VAR2, VAR3..........................VAR84

still i am not getting the desired output

Rows 1-1

Total rows: 1Total columns: 3

COMPANY_NAME

VAR1

VAR2

2.2

my output is like above

Re: how to convert multiple variables into numeric ?

Kurt_Bremser — Tue, 05 Oct 2021 11:36:30 GMT

So the usual issue of using the worst file format for data transfer.

The best solution: avoid Excel files, save the data to text files (csv) from Excel and read this with a data step where you keep total control over the data types and can deal with special non-numeric values by using a custom informat.

Re: how to convert multiple variables into numeric ?

srikanthyadav44 — Wed, 06 Oct 2021 04:56:36 GMT

As you have suggested, i have saved my excel file in CSV format and uploaded it into SAS.

still, i am getting the same problem

Alphabetic List of Variables and Attributes# Variable Type Len Format Informat44454647484950515253545556575859606162636465666768697071727374757677787980818283848523242526272829303132333435363738394041424323456789101112131415161718192021221

ESG2000	Num	8	BEST12.	BEST32.
ESG2001	Num	8	BEST12.	BEST32.
ESG2002	Char	1	$1.	$1.
ESG2003	Char	1	$1.	$1.
ESG2004	Char	1	$1.	$1.
ESG2005	Char	1	$1.	$1.
ESG2006	Char	1	$1.	$1.
ESG2007	Char	1	$1.	$1.
ESG2008	Char	1	$1.	$1.
ESG2009	Char	1	$1.	$1.
ESG2010	Char	1	$1.	$1.
ESG2011	Char	1	$1.	$1.
ESG2012	Char	1	$1.	$1.
ESG2013	Char	1	$1.	$1.
ESG2014	Char	1	$1.	$1.
ESG2015	Char	1	$1.	$1.
ESG2016	Char	1	$1.	$1.
ESG2017	Char	1	$1.	$1.
ESG2018	Char	1	$1.	$1.
ESG2019	Char	1	$1.	$1.
ESG2020	Char	1	$1.	$1.
Environment2000	Num	8	BEST12.	BEST32.
Environment2001	Num	8	BEST12.	BEST32.
Environment2002	Char	1	$1.	$1.
Environment2003	Char	1	$1.	$1.
Environment2004	Char	1	$1.	$1.
Environment2005	Char	1	$1.	$1.
Environment2006	Char	1	$1.	$1.
Environment2007	Char	1	$1.	$1.
Environment2008	Char	1	$1.	$1.
Environment2009	Char	1	$1.	$1.
Environment2010	Char	1	$1.	$1.
Environment2011	Char	1	$1.	$1.
Environment2012	Char	1	$1.	$1.
Environment2013	Char	1	$1.	$1.
Environment2014	Char	1	$1.	$1.
Environment2015	Char	1	$1.	$1.
Environment2016	Char	1	$1.	$1.
Environment2017	Char	1	$1.	$1.
Environment2018	Char	1	$1.	$1.
Environment2019	Char	1	$1.	$1.
Environment2020	Char	1	$1.	$1.
Governance2000	Num	8	BEST12.	BEST32.
Governance2001	Num	8	BEST12.	BEST32.
Governance2002	Char	1	$1.	$1.
Governance2003	Char	1	$1.	$1.
Governance2004	Char	1	$1.	$1.
Governance2005	Char	1	$1.	$1.
Governance2006	Char	1	$1.	$1.
Governance2007	Char	1	$1.	$1.
Governance2008	Char	1	$1.	$1.
Governance2009	Char	1	$1.	$1.
Governance2010	Char	1	$1.	$1.
Governance2011	Char	1	$1.	$1.
Governance2012	Char	1	$1.	$1.
Governance2013	Char	1	$1.	$1.
Governance2014	Char	1	$1.	$1.
Governance2015	Char	1	$1.	$1.
Governance2016	Char	1	$1.	$1.
Governance2017	Char	1	$1.	$1.
Governance2018	Char	1	$1.	$1.
Governance2019	Char	1	$1.	$1.
Governance2020	Char	1	$1.	$1.
Social2000	Num	8	BEST12.	BEST32.
Social2001	Num	8	BEST12.	BEST32.
Social2002	Char	1	$1.	$1.
Social2003	Char	1	$1.	$1.
Social2004	Char	1	$1.	$1.
Social2005	Char	1	$1.	$1.
Social2006	Char	1	$1.	$1.
Social2007	Char	1	$1.	$1.
Social2008	Char	1	$1.	$1.
Social2009	Char	1	$1.	$1.
Social2010	Char	1	$1.	$1.
Social2011	Char	1	$1.	$1.
Social2012	Char	1	$1.	$1.
Social2013	Char	1	$1.	$1.
Social2014	Char	1	$1.	$1.
Social2015	Char	1	$1.	$1.
Social2016	Char	1	$1.	$1.
Social2017	Char	1	$1.	$1.
Social2018	Char	1	$1.	$1.
Social2019	Char	1	$1.	$1.
Social2020	Char	1	$1.	$1.
company_name	Char	39	$39.	$39.

Re: how to convert multiple variables into numeric ?

srikanthyadav44 — Wed, 06 Oct 2021 05:03:58 GMT

i have removed the numeric variables, now my file as only character variables.

please suggest how to convert all the character variables in the file into numeric in one step.

thanks in advance

Re: how to convert multiple variables into numeric ?

SASKiwi — Wed, 06 Oct 2021 05:22:13 GMT

Please post the complete SAS log of your program to import your CSV. Once you have done that we can explain how to change it to fix your problem.

Re: how to convert multiple variables into numeric ?

Kurt_Bremser — Wed, 06 Oct 2021 06:15:48 GMT

Do NOT use PROC IMPORT to read csv files. Write the data step yourself.

In your case, the INPUT statement will be quite easy:

input
  (esg2000-esg2020 environment2000-environment2020 governance2000-governance2020 social2000-social2020) (.best32.)
  company_name :$39.
;