topic Re: SAS update table format in SAS Data Management

SAS update table format

Citrine10 — Wed, 18 May 2022 12:40:57 GMT

Hi there I have the following dataset where the column name is called Text and the datatype of this column is numeric. How do I convert any column that has the name Text to a Character datatype?

Initial dataset:

Age	Gender	Text
3	F	45
4	M	63
4	M	88
7	M	90

I have use the below to identify the datatypes of the columns and I store this data in a table called myd

proc iml;
tClass = TableCreateFromDataSet("Work", "mycsv");
colnames = TableGetVarName(tClass);
type = TableGetVarType(tClass);
isNum = TableIsVarNumeric(tClass);
print (type // char(isNum))[c=colnames r={"Type" "IsNum"}];
create myd var {colnames type};
append;
close myd;

Then I perform a check:

proc sql;
create table test as 
select * ,
case when colnames like 'Text%' and Type <> 'C' then  'C'
else Type
end as NewType
from myd
;quit;

result:

COLNAMES	TYPE	NewType
String	N	C

Now that I can see the types and am able to identify which columns need an update, I am not sure how to instruct my initial dataset to convert my column called Text from an numeric to Character.

Re: SAS update table format

ballardw — Wed, 18 May 2022 13:44:47 GMT

I would first ask why a data set that you want a variable to be numeric was created as text. If you make sure that doesn't happen then you prevent problems instead of having to continuously fix them.

An extremely high percentage of data type problems like this arise from not controlling how data is brought into SAS. How did you read that data into a SAS data set in the first place? It may be easier to reread the data than to "fix" it afterward.

Re: SAS update table format

Citrine10 — Wed, 18 May 2022 13:55:49 GMT

hi, it can not be controlled as the are thousands of csv files loaded by various parties

Re: SAS update table format

mkeintz — Wed, 18 May 2022 14:34:24 GMT

Do you expect to report minimums, maximums, averages, variances, etc. of these numeric values masquerading as text? Not even min or max in this list of stats can be reliably produced from numeric character values.

If there is future analysis to be done, then make the variable into a type that represents ongoing usage rather that the vagueries of what appears to be "crowd-sourced" data.

Re: SAS update table format

Tom — Wed, 18 May 2022 14:37:34 GMT

@Citrine10 wrote:
hi, it can not be controlled as the are thousands of csv files loaded by various parties

Can you explain more what the issue is?

It is very easy to control how variables are defined when reading a CSV file. Those are just simple delimited text files, so just write a data step to read the file and you have full control over the names and types of the variables.

Re: SAS update table format

Tom — Wed, 18 May 2022 14:42:52 GMT

If you want to convert a number into text you need decide how your want it formatted.

One way is to just use whatever format the variable already had assigned to. This is really easy with VVALUE() function.

data want;
  set have;
  new_text = vvalue(text);
run;

Re: SAS update table format

ballardw — Wed, 18 May 2022 15:21:45 GMT

@Citrine10 wrote:
hi, it can not be controlled as the are thousands of csv files loaded by various parties

It has nothing to do with who uploaded them, it is the process used to READ the CSV files. That is controlled by you or the recipients.

Show the code you are using to read ONE of these files. At least.

Re: SAS update table format

Ksharp — Thu, 19 May 2022 01:48:59 GMT

Calling @Rick_SAS

Re: SAS update table format

Kurt_Bremser — Thu, 19 May 2022 06:18:12 GMT

@Citrine10 wrote:
hi, it can not be controlled as the are thousands of csv files loaded by various parties

No issue at all. CSV files are read with DATA steps, where you have full control over how data is read.

Do not use PROC IMPORT, particularly not when you need consistency.

Re: SAS update table format

Kurt_Bremser — Thu, 19 May 2022 06:46:02 GMT

@Ksharp wrote:

Calling @Rick_SAS

This is not a IML issue. The code here is only a very complicated replacement for a simple query against DICTIONARY.COLUMNS.

@Citrine10 :

proc sql;
create table test as
  select
    name,
    type,
    case 
      when name like 'Text%' and Type <> 'C' then  'C'
      else Type
    end as Newtype
  from dictionary.columns
  where libname = "WORK" and memname = "MYCSV"
;
quit;

Re: SAS update table format

RacheLGomez123 — Wed, 21 Dec 2022 07:46:53 GMT

The General Process for Software Updates
Step 1: Learn about the Availability of a New Release.
Step 2: Run the SAS System Evaluation Tool.
Step 3: Run a Deployment Registry Report.
Step 4: Assess the Impact of Upgrading the Software at Your Site.
Step 5: Request a New Product Release or Maintenance Release.
Regards,
Rachel Gomez