topic Re: PROC FORMAT when importing data from excel in SAS Programming

PROC FORMAT when importing data from excel

efwalters — Thu, 28 Jun 2018 23:04:52 GMT

I am working on an assignment and I need to import an excel file into SAS and then randomly select different variables. I have tried using proc format to code the variables but it isn's working.. This is the code I currently have:

options nodate nonumber;

libname computerapps "C:\Users\efwalters\Documents\computerapps";

proc import out=Project3

datafile = "C:\Users\efwalters\Documents\computerapps\Project3.xlsx"

dbms=xlsx replace;

getnames=yes;

run;

proc format;

value $institutef 1 = 'Cancer Institute 1'

2 = 'Cancer Institute 2'

3 = 'Cancer Institute 3'

4 = 'Cancer Institute 4'

5 = 'Cancer Institute 5';

value $cancertypef 1 = 'Acute Lymphocytic Leukemia (ALL)'

2 = 'Acute Myeloid Leukemia (AML)'

3 = 'Chronic Lymphocutic Leukemia (CLL)'

4 = 'Chronic Myeloid Leukemia (CML)';

proc print data=project3;

run;

Re: PROC FORMAT when importing data from excel

ballardw — Thu, 28 Jun 2018 23:14:36 GMT

First thing is if you want to see the formatted values you have to apply the format to appropriate variable. You didn't mention any variable name but it might look something like

proc print data=project3;

format cancertype $cancertypef. institute $institute. ;

run;

Second is that the format TYPE has to match the variable. If the variable(s) you attempt to use the $cancertypef. format with are numeric then you will get an error about format type not matching the variable type. The $ on the value statement tells SAS the variable with contain character data.

"Isn't working" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... 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 {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: PROC FORMAT when importing data from excel

efwalters — Fri, 29 Jun 2018 01:42:00 GMT

Thank you for you input!

I tried to add in what you included, but my output still includes the original values not the numeric values that I wrote into the proc format code.

No errors or warnings popup in the log window...

Re: PROC FORMAT when importing data from excel

Reeza — Fri, 29 Jun 2018 01:45:19 GMT

Post your full code and log.
Also, did you verify the types aligned as indicated in BallardW's post?

Re: PROC FORMAT when importing data from excel

ballardw — Fri, 29 Jun 2018 15:19:18 GMT

@efwalters wrote:

Thank you for you input!

I tried to add in what you included, but my output still includes the original values not the numeric values that I wrote into the proc format code.

No errors or warnings popup in the log window...

Actual data is important when comes to format. The values have to actually correspond to what is in the dataset.

data example;
   code = ' 1';
run;

proc print data=example;
   format code $institutef.;
run;

Shows 1 in the output for code because the actual value has a leading space. That does not match the definition of the format which does not allow for leading spaces the way you have defined it. The format would work for a value of '1 ' because the value for string comparisons matches.

Re: PROC FORMAT when importing data from excel

efwalters — Sat, 30 Jun 2018 13:57:36 GMT

I was able to format the variables the way I was hoping, but now I am having an issue with using the "IF statement"

This is the code I currently have:

options nodate nonumber;

proc format;

value $institutef 'Cancer Institute 1' = 'CI1'

'Cancer Institute 2' = 'CI2'

'Cancer Institute 3' = 'CI3'

'Cancer Institute 4' = 'CI4'

'Cancer Institute 5' = 'CI5';

value $cancertypef 'Acute Lymphocytic Leukemia (ALL)' = 'ALL'

'Acute Myeloid Leukemia (AML)' = 'AML'

'Chronic Lymphocutic Leukemia (CLL)' = 'CLL'

'Chronic Myeloid Leukemia (CML)' = 'CML';

proc import out=Project3

datafile = "C:\Users\efwalters\Documents\computerapps\Project3.xlsx"

dbms=xlsx replace;

getnames=yes;

run;

data project3;

set project3;

format leu_type $cancertypef. inst_num $institutef.;

run;

data tempp;

set project3;

if inst_num = CI1;

data temp1;

set tempp;

if leu_type = ALL;

x = ranuni(0);

output;

proc sort data=temp1;

by x;

run;

data one;

set temp1 (obs=5);

run;

proc print data=one;

run;

And in the log window, this note appears: "NOTE: Variable CI1 is uninitialized."

Re: PROC FORMAT when importing data from excel

Tom — Sat, 30 Jun 2018 14:18:16 GMT

And in the log window, this note appears: "NOTE: Variable CI1 is uninitialized."

That error message is very clear. In this block of code:

data tempp; 
set project3; 
if inst_num = CI1;

You are testing if the variable INST_NUM is equal to the variable CI1. SAS is saying that there is no variable named CI1.

From the rest of your program it looks like you probably wanted to test if the formatted value of INST_NUM was equal to the string literal 'CI1'.

if put(inst_num,$institutef.)='CI1';