BookmarkSubscribeRSS Feed
india2016
Pyrite | Level 9

base data set and append data set having different variable length and force option is not working.Is there any solution to get proper variable length?

10 REPLIES 10
Ksharp
Super User

proc sql;

create table want as

 select *

  from one 

union all corr

 select *

  from two;

quit;

Reeza
Super User

What does not working mean? 

india2016
Pyrite | Level 9

Book1

 

Gender
female
male

 

 

Book2

 

Gender
female
transgender
male

 

 

 I have this two datasets in excel format.After importing these two excel sheets I want to append book1 & book2.

If I use append table in SAS EG then I got  "transg"  which takes the length of "female" from book2.

 

What should I do to get the right result?("transg" as transgender)

 

Reeza
Super User

Set dataset 2 as the base and dataset 1 as the data. 

 

Ideally, make your base table with desired characteristics (length, type, format) first and use that in proc append. 

india2016
Pyrite | Level 9
but what if there are many tables and if we don't know characteristics?
Reeza
Super User

1. Compare all datasets and determine max length required using Dictionary tables. Create a Base dataset with these characteristics that's empty to start. 

2. Append using a data step or SQL union. You may still run into truncation issues. 

3. Create Base data set with arbitrarily large lengths and truncate later. 

 

I would suggest #1. 

india2016
Pyrite | Level 9

we have 128 excel files in each file there are 20 variables? 

ballardw
Super User

@india2016 wrote:

we have 128 excel files in each file there are 20 variables? 


This tells me that you are likely using Proc Import or similar that lets EXCEL controll the reported lengths of variables.

One approach if you know, or have a reasonable guess as to the lengths the values should take is to make a dummy set with the maximum length and append each one to that.

 

Data dummy;

   length var1 $ 25 var2 var3 $ 10 ;

   output;

run;

will create a data set with 3 variables with the lengths indicated.

 

I you have many data sets and the structures are the same you may want to use a SET statement to combine all of them at the same time:

 

data want;

     set DUMMY

           dataset1

           datasetq

           datasetabc;

run;

 

If you name the sets nicely you can even use a short cut list:

 

data want;

    set dummy dataset1 - dataset20;

run;

rogerjdeangelis
Barite | Level 11

Taking care to get the maximum length of a excel string before importing

Taking care to get the maximum length of a excel string before importing

inspired by
https://goo.gl/fiZXXR
https://communities.sas.com/t5/Base-SAS-Programming/Append-multiple-dataset/m-p/344558

Issue
I have this two datasets in excel format.After importing these
two excel sheets I want to append book1 & book2.
If I use append table in SAS EG then I got  "transg"
which takes the length of "female" from book2.


You may sometimes get away without specifying character length, but it is
better to passthru and check the lengths. I suspect
odbc passthru may only support length upto 255 bytes.
No sure of OLE-DB. You ned to passthru for this issue?

HAVE ( two sheets in one workbook )
====================================


d:/xls/books.xlsx sheet BOOK1

    +------------------+
    |        A         |
    +------------------+
1   |      BOOK        |
    +------------------+
2   |     Gender       |
    +-------------------
3   |     female       |
    +-------------------
4   |      male        |
    +-------------------

[BOOK1]


d:/xls/books.xlsx sheet BOOK2

    +------------------+
    |        A         |
    +------------------+
1   |      BOOK        |
    +------------------+
2   |     Gender       |
    +-------------------
3   |     female       |
    +-------------------
4   |  transgender     |
    +-------------------
4   |      male        |
    +-------------------

[BOOK2]

WANT   (following SAS dataset )
================================

p to 40 obs from want total obs=7

bs    BOOK

1     Gender
2     female
3     male
4     Gender
5     female
6     transgender
7     male

WORKING CODE

      connect to excel (Path="d:\xls\books.xlsx");
      select max(len(book)) as lenmax

*                _                  _       _
 _ __ ___   __ _| | _____        __| | __ _| |_ __ _
| '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` |
| | | | | | (_| |   <  __/_____| (_| | (_| | || (_| |
|_| |_| |_|\__,_|_|\_\___|      \__,_|\__,_|\__\__,_|

;


%utlfkil(d:/xls/books.xlsx); * delete if exists;
libname xel "d:/xls/books.xlsx";

data xel.book1;
  informat book $11.;
  input Book ;
cards4;
Gender
female
male
;;;;
run;quit;


data xel.book2;
  informat book $11.;
  input Book ;
cards4;
Gender
female
transgender
male
;;;;
run;quit;

*          _       _   _
 ___  ___ | |_   _| |_(_) ___  _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|

;

* notes
   you need bckslashes for this to work d:\xls\books.xlsx
   if uncomfortable with DOSUBL just pull the code out
   and run outside the datastep
;


%symdel maxlen;
libname xel "d:/xls/books.xlsx";
data want;

  if _n_=0 then do;
    %let rc=%sysfunc(dosubl(%nrbquote(
        proc sql dquote=ansi;
           connect to excel (Path="d:\xls\books.xlsx");
             select max(lenmax) into :maxlen separated by ' ' from connection to Excel
                 (
                  Select
                       max(len(book)) as lenmax
                  from
                       book1
                  union
                  select
                       max(len(book)) as lenmax
                  from
                       book2
                 );
             disconnect from Excel;
        quit;
        libname xel "d:/xls/books.xlsx";
        run;quit;
       )));
   end;

   set
     xel.book1(dbsastype=(book="char(&maxlen.)"))
     xel.book2(dbsastype=(book="char(&maxlen.)"))
   ;
run;quit;

libname xel clear;



                Variables in Creation Order

#    Variable    Type    Len    Format    Informat    Label

1    BOOK        Char     11    $6.       $6.         BOOK



rogerjdeangelis
Barite | Level 11
If you do not have named ranges book1, book2 use
'[shee1$]' within the passthru

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 13709 views
  • 3 likes
  • 5 in conversation