Default variable lengths & including duplicates.

[a_dobbins]

First off let me just say i am quite a novice with SAS and i'm using the studio version and studying it through a work scheme / online modules.

I am using a sas program which i have written to import 2 sheets and merge them together which it does successfully (to a point)

However i am having an issue whereby the column it inserts the data (similar to how a vlookup works) doesn't allow me to specify a length when creating it this column is totally empty in my input sheet as i then get the value from the other sheet. The error i get is

NOTE: The LENGTH attribute cannot be changed and is therefore being ignored.

and the code i am using is

proc import replace datafile= '/folders/myfolders/arron/tab.xlsx'

DBMS=xlsx OUT = work.tab;

length agent $ 25;

run;

I am also then experiencing an issue whereby it lists the first value only and then moves onto the next agent leaving lots of blanks.

my code is

data arron.tab;

merge tab (in=a) agent_stores (in=b keep=store agent);

    by store;

    if a;  

run;

Store is the variable i reference within the agent_stores list.

Any help appreciated.

[Reeza]

You can't set the length of a variable in a proc import. You can add it after the import step in a data step or even your merge. It's hard to say about the merge, can you show an example of what's happening. You may need a SQL merge.

proc import replace datafile= '/folders/myfolders/arron/tab.xlsx'

DBMS=xlsx OUT = work.tab;

run;

data tab2;

set tab;

length agent $25.;

run;

[Tom]

Using XSLX for your data can cause this type of problem because that is a spreadsheet and not a database.  So SAS has to guess at the variable types and lengths.  You cannot actually change that decision by adding a LENGTH statement to PROC IMPORT.  You would be better off adding code to force the variables to comply with your data specifications.

data agent_stores_fixed ;

  length agent $25 store $25 ;

  set agent_stores ;

run;

proc sort data=agent_stores_fixed; by store; run;

data tab_fixed;

  length store $25 ;

  set tab;

run;

proc sort data=tab_fixed; by store; run;

data want ;

merge tab_fixed (in=in1) agent_stores(keep=store agent);

by store ;

if in1 ;

run;

It is not clear what you mean by the merge not working, but the description sounds like a problem you might have if both tables have more than one row per store.  You cannot do a many to many merge using a simple data step.

[a_dobbins]

Thanks Both. I have tried setting the lengths later on and the result is that the length is already set.

When i use a proc contents to view how its set up its shows the length as being 1 which is as a result of this column being blank in the excel sheet.

With the merge what i mean is i have 2 lists the agent_stores which is a store list and then agent name.

with the tab list this shows 7 variables. Including the store name and a blank space for the agent name.

It inputs the agent name once for the store (well the first character anyway) and then leaves each one afterwards blank.

[Tom · Accepted solution]

To change the length you need to put the LENGTH statement before any other statement (like the SET or MERGE) that would define a length for the variable. Otherwise the LENGTH statement is ignored because the length has already been defined.

You need to DROP the empty column.  The way that the MERGE statement manages to do a 1 to MANY merge is that the values from the 1 dataset are retained as new records from the MANY dataset are read.  But if the MANY dataset has the same variable then the value read from that dataset will overwrite the value that was retained.

I think that just adding the DROP will fix both problems. The variable AGENT in ARRON.TAB is being defined as length=1 because the first definition of it is in data set TAB.

data arron.tab;

merge tab (in=a drop=agent) agent_stores (in=b keep=store agent);

    by store;

    if a; 

run;

[a_dobbins]

Great! That fixed both problems. Much appreciated Tom!