Join Now

Smart import af flere ark fra samme excel-fil

by Contributor SanderEhmsen on ‎10-31-2014 05:36 AM (1,794 Views)

Hej Community!

Vi er i Odense Kommune ved at samle uhyrlige mængder data omkring ejendomsskatter manuelt, men systematisk. Historien er den simple, at vi kunne få assistance til opgaven fra en endog meget dygtig og omhyggelig praktikant, og derved kunne spare over 30.000 kr. ved at gøre opgaven manuelt i stedet for at købe udtrækket fra en udbyder.

Nå. Sagen er den, at for at kunne sikre nok systematik, har vi samlet data i mange ark. Men nu sidder vi og bøvler med en smart måde at importere data på.

Det er nemt nok at bruge EG til at importere og kopiere import-steppet, som det bl.a. vises her: https://www.youtube.com/watch?v=DU0he9lKVyg

Men... Vi har måske 100 ark per excel-fil, og vi har otte excel-filer. Det vil være et enormt (kedeligt) arbejde at gøre som David Ghan i ovenstående link.

Derfor tænkte jeg, at det må man simpelthen kunne skrive sig ud af. Vi vil som David i ovenstående link gerne have importeret samtlige ark og derefter appended dem til een sammenhængende tabel.

Det, jeg drømmer om, er en kodestump, som upåagtet hvad arkene hedder importerer dem alle sammen og derefter appender dem til én tabel.

Altså noget af jeres magi, der gør, at jeg ikke selv skal navngive alle arkene. For det er næsten en umulig opgave.

Er det noget, "man" kan?

Jeg har søgt at anonymisere data ved at fjerne de kritiske kolonner.

Vi venter enormt spændt på et svar!

Comments
by SAS Employee GeorgMorsing
on ‎10-31-2014 01:00 PM

Hej Sander,

Ja det kan du godt. Ikke sådan lige - ud af boksen - det vil kræve lidt kode. Men først må jeg vide om alle faneblade i regnearket har samme struktur. Det vil jeg næsten forvente, for ellers giver det ikke mening at appende dem. Og ja så må vi igang med macro kode. Løsningen vil indeholde:

- Libname statement til din excel fil

- proc copy hvor alle tabeller kopieres til SAS (dette step er ikke helt nødvendigt, men lad os bare gøre det)

- append data kan nu ske med

          A. et data step og set statement, hvor alle data læses på en gang

          B. mange proc append, hvor man langsom lægger et dataset til hver gang

- uanset om man vælger A eller B, så skal der nok lige lidt makro kode til, som finder navn på tabellerne, svarende til navne på faneblade i regneark. Dette information hentes fra dictionary tabellerne

Det hele kan automatiseres, så dette kan genbruges på et vilkårligt regneark.

Svar lige på mit spørgsmål: har alle faneblade ens struktur ?

        

Så vil jeg efterfølgende kode løsningen til dig. Det tager nok 20-30 min. Men en sjov opgave. Og en opgave mange SAS brugere kan genbruge - derfor vil jeg gerne lave den til dig.

Georg

PS: Du må altså igang med makro programmering

by Contributor SanderEhmsen
on ‎11-03-2014 03:31 AM

Hej Georg

Tak for tilbuddet! Det er noget af en service.

Strukturen af data er næsten ens. Der er ikke altid de samme kolonner i samtlige datasæt. Dette skyldes, at hvis der ikke har været nogen observation med værdier på den konkrete variabel, får vi ikke kolonnen med i udtrækket. Det vil altså sige, at vi principielt godt kan have et sheet med 33 kolonner og et med 35 kolonner, men at det med 35 kolonner ikke rummer samtlige kolonner i det datasæt med 33 kolonner. Det er ikke et problem i selve appending-delen, har jeg undersøgt - fordi SAS så rigtig nok bare formår at gøre værdierne i de resterende kolonner missing.

Er det svar nok?

Og ja, jeg skal i gang med makroprogrammering. Det har stået på min udviklingsliste i snart et halvt år. I mit nære udviklingsmiljø er der imidlertid ingen, der kan makroprogrammering, og jeg har derfor ikke umiddelbart kunnet finde en passende sparringspartner.

Men mon ikke youtube har en hel masse nyttige sessioner liggende, som jeg kan tage udgangspunkt i. Jeg tror dog, at jeg først skal forstå data-steppe ordenligt, før det giver mening med makroer. Jeg får booket mig i kalenderen til at bruge tid på det i løbet af ugen - det er nok givet godt ud :-).

Mange hilsner,

Sander.

by SAS Employee GeorgMorsing
on ‎11-03-2014 02:01 PM

Hej Sander,

Her er en løsning. Det var lettere end jeg lige regnede med. Jeg har kun testet på begrænsede data mængder, så der kan opstå ting jeg ikke lige har forudset.

  • Libname giver adgang til dynamisk at læse alle faner i et regneark
  • sashelp.vtable er en af dictionary tabellerner, hvor man kan få information om alle tabeller, der er adgang til
  • der opbygges en tekst der indeholder navnet på alle tabeller i MYEXCEL, svarende til alle fanerne
  • Call symput danner en macro variable, så så benyttes i SET statement til på én gang at læse alle faner i regnearket         

libname myexcel pcfiles path='C:\Georg\Klog på SAS\Tips\regneark med mange faneblade.xlsx';

data _null_;

                length table_all $ 10000;

                retain table_all;

                set sashelp.vtable (keep = memname libname) end = last_obs;

                where libname = 'MYEXCEL';

                table_all = cat(trim(table_all)," myexcel.'",trim(memname),"'n");

                if last_obs = 1 then call symput('Faner',table_all);

run;

data alt_i_en_tabel;

                  set &faner;

run;

libname myexcel clear;

Dette kræver produktet SAS/ACCESS to PC Files. Det er jeg sikker på I har licens til i Odense Kommune. Og så kræver det at du har LIBNAME adgang til din Excel version. Jeg benytter en 64-bit version af SAS og en 32-bit version af Excel. Derfor har jeg SAS PC File Server installeret og benytter ovenstående LIBNAME statement. SAS PC File server sørger for at 64-bit SAS kan løse 32-bit Excel. SAS PC File Server kan downloades direkte fra denne SAS web site:

SAS Support Downloads Browse

Hvis SAS og Excel benytter samme bit version (32 eller 64) så skal du bare benytte dette statement til erstatning af ovenstående libname statement:

libname myexcel excel 'C:\Georg\Klog på SAS\Tips\regneark med mange faneblade.xlsx';

God fornøjelse Sander. Hvis det driller så sig til.

Georg           

by Contributor SanderEhmsen
on ‎11-04-2014 08:37 AM

Hej Georg

Tusind tak for dit svar.

Desværre driller det. Jeg har forsøgt at følge beskrivelsen til at få PC File Server til at køre, men det er ikke lykkedes mig at få det til at virke. Ligesom du kører jeg en 32 bit version af Excel, men jeg kører også en 32 bit version af SAS EG - kan det have noget med noget at gøre?

Jeg har bedt SAS Files Server om at køre, når jeg starter computeren op. Og jeg har forsøgt at genstarte computeren. Men jeg kan ikke få det programmet til at virke. Jeg får følgende fejl:

16         libname myexcel pcfiles path='\\s00606p\H_DFS_BMF\Økonomi og Analyse\Analyse\SAEHM\SAS\ESR\ESR 0-100.000.xlsx';

ERROR: Unable to locate pcfserver.exe. Please install PC Files Server.

ERROR: Error in the LIBNAME statement.

Fordi både SAS og Excel kører på 32 bit, har jeg prøvet at gå uden om pcfiles og simpelthen bare skrive:

libname myexcel excel path='\\s00606p\H_DFS_BMF\Økonomi og Analyse\Analyse\SAEHM\SAS\ESR\ESR 0-100.000.xlsx'

- men uden held. Der får jeg følgende log:

1 The SAS System 14:21 Tuesday, November 4, 2014

1          ;*';*";*/;quit;run;

2          OPTIONS PAGENO=MIN;

3          %LET _CLIENTTASKLABEL='Program';

4          %LET _CLIENTPROJECTPATH='H:\BMF\Økonomi og Analyse\Analyse\SAEHM\SAS\ESR\Georg2.egp';

5          %LET _CLIENTPROJECTNAME='Georg2.egp';

6          %LET _SASPROGRAMFILE=;

7         

8          ODS _ALL_ CLOSE;

9          OPTIONS DEV=ACTIVEX;

10         GOPTIONS XPIXELS=0 YPIXELS=0;

11         FILENAME EGSR TEMP;

12         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=HtmlBlue

12       ! STYLESHEET=(URL="file:///C:/Programmer/SASHome/x86/SASEnterpriseGuide/5.1/Styles/HtmlBlue.css") NOGTITLE NOGFOOTNOTE

12       ! GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

13        

14         GOPTIONS ACCESSIBLE;

15         libname myexcel excel path='\\s00606p\H_DFS_BMF\Økonomi og Analyse\Analyse\SAEHM\SAS\ESR\ESR 0-100.000.xlsx';

NOTE: Libref MYEXCEL was successfully assigned as follows:

      Engine:        EXCEL

      Physical Name: \\s00606p\H_DFS_BMF\Økonomi og Analyse\Analyse\SAEHM\SAS\ESR\ESR 0-100.000.xlsx

16        

17         data _null_;

18        

19                         length table_all $ 10000;

20        

21                         retain table_all;

22        

23                         set sashelp.vtable (keep = memname libname) end = last_obs;

24        

25                         where libname = 'MYEXCEL';

26        

27                         table_all = cat(trim(table_all)," myexcel.'",trim(memname),"'n");

28        

29                         if last_obs = 1 then call symput('Faner',table_all);

30        

31         run;

WARNING: Failed to scan text length or time type for column '1-8983$'.Ydelse-1.

WARNING: Failed to scan text length or time type for column '36000-36999$'.Ydelse-1.

WARNING: Failed to scan text length or time type for column '41000-41999$'.Ydelse-1.

WARNING: Failed to scan text length or time type for column '95000-96999$'.Ydelse-1.

NOTE: There were 78 observations read from the data set SASHELP.VTABLE.

      WHERE libname='MYEXCEL';

NOTE: DATA statement used (Total process time):

      real time           1:53.84

      cpu time            2:01.07

     

32        

33         data alt_i_en_tabel;

34        

35 set &faner;

NOTE: Line generated by the macro variable "FANER".

35            myexcel.''14977-15408$''n myexcel.''15410-19999$''n myexcel.''20000-22999$''n myexcel.''23000-23999$''n

              ________  _____

              22        22

              201       76

2 The SAS System 14:21 Tuesday, November 4, 2014

35       ! myexcel.''24000-25357$''n myexcel.''25359-26499$''n myexcel.''26500-27499$''n myexcel.''27500-28999$''n

35       ! myexcel.''29000-29999$''n

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, ;, END, INDSNAME, KEY, KEYS, NOBS, OPEN,

              POINT, _DATA_, _LAST_, _NULL_. 

ERROR 201-322: The option is not recognized and will be ignored.

ERROR 76-322: Syntax error, statement will be ignored.

36        

37         run;

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.ALT_I_EN_TABEL may be incomplete.  When this step was stopped there were 0 observations and 0 variables.

WARNING: Data set WORK.ALT_I_EN_TABEL was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

     

38        

39         libname myexcel clear;

NOTE: Libref MYEXCEL has been deassigned.

40        

41         GOPTIONS NOACCESSIBLE;

42         %LET _CLIENTTASKLABEL=;

43         %LET _CLIENTPROJECTPATH=;

44         %LET _CLIENTPROJECTNAME=;

45         %LET _SASPROGRAMFILE=;

46        

47         ;*';*";*/;quit;run;

48         ODS _ALL_ CLOSE;

49        

50        

51         QUIT; RUN;

52        

Er det noget, du kan gennemskue?

Det er garanteret bare mig, der ikke kan lure at få installeret PC Files Server ordenligt - men det gør jo ikke problemet mindre præsent :-).

by Contributor SanderEhmsen
on ‎11-10-2014 04:50 AM

Georg og jeg fortsatte samtalen over telefon og mail. Vi kom frem til, at der i nogle tilfælde skal stå

libname myexcel excel path='\\s00606p\H_DFS_BMF\Økonomi og Analyse\Analyse\SAEHM\SAS\ESR\ESR 0-100.000.xlsx'  mixed=yes;

data _null_;

                length table_all $ 10000;

                retain table_all;

                set sashelp.vtable (keep = memname libname) end = last_obs;

                where libname = 'MYEXCEL';

                table_all = cat(trim(table_all)," myexcel.",trim(memname),"n"); /*nogle gange skald er være ' ' uden om trim-funktionen, og andre gange ikke. Det kommer an på formen på navnene på arkene/*

                if last_obs = 1 then call symput('Faner',table_all);

run;

data alt_i_en_tabel;

                  set &faner;

run;

libname myexcel clear;

Endelig havde vi et større problem med, at excel-filen ikke var særlig godt struktureret mht. form på variablene. Excel-filen havde den uvane, at hvis der ikke står noget i en kolonne, var excel-filen struktureret således, at der i stedet for en tom celle var et vist antal mellemrum.

Mit follow-up-spørgsmål er derfor: Er der mulighed for at definere, at alle variable skal være tekst-strenge. Så kan jeg inde i SAS omforme de relevante til numeriske variable efterfølgende - og på den måde få alle dem, der fejlagtigt læses som indeholdende mellemrum til at være missing - som de jo i virkeligheden er.



by SAS Employee MichaelLarsen
on ‎11-13-2014 06:01 AM

Hej Sander,

Det kan man godt ved at bruge dataset option DBSASTYPE.

I dit eksempel skal man så anvende sashelp.vcolumn i stedet for og generere dbsastype for de enkelte kolonner i hver tabel, du ved jo ikke om alle kolonner altid er tilstede.

Et simpelt eksempel hvor tabellen SASHELP.CLASS er eksporteret til en Excel fil og hvor man så ved indlæsning til SAS tvinger de numeriske kolonner til at blive karakter kolonner med længden 10:

data test;

  set xls.class(dbsastype=( age='char(10)' height='char(10)' weight='char(10)' ));

run;

by Contributor ErikLund_Jensen
on ‎11-28-2014 08:12 AM

Hej Alle

Pas på med SAS Excel libname engine. Den ser ikke nødvendigvis alle ark i en excel-fil.

I Sanders log ovenfor kan man se, at hvis han kører Georgs kode, kommer følgende 4 warnings:

     WARNING: Failed to scan text length or time type for column '1-8983$'.Ydelse-1.

     WARNING: Failed to scan text length or time type for column '36000-36999$'.Ydelse-1.

     WARNING: Failed to scan text length or time type for column '41000-41999$'.Ydelse-1.

     WARNING: Failed to scan text length or time type for column '95000-96999$'.Ydelse-1.

Denne warning medfører, at SAS ikke kan se de pågældende ark. De er ikke med i sashelp.vtable og kan heller ikke ses af proc contents. Libnamet  har iflg. SAS 78 members, men der er 82 ark i excelfilen, så et loop hen over memberlisten medfører tab af data.

De ark, SAS ikke kan se via libname-engine, kan den godt finde ud af at importere med proc import, men man er nødt til få fat i arknavnene på en anden måde. I det konkrete tilfælde blev det løst med en excel-makro, der dannede et nyt ark med en liste over alle ark i filen. Dette nye ark blev så importeret og brugt som basis for et loop, der importerede de øvrige ark.

by SAS Employee MichaelLarsen
on ‎11-29-2014 12:36 PM

Hej Erik,

Det er et kendt problem med Microsoft driverne som SAS kalder.

Det skyldes at driveren default skanner et antal rækker og ud fra indholdet bestemmer hvilken datatype kolonnen er.

I nogle tilfælde tager den fejl ud fra det antal rækker der blev læst.

Løsningen er at rette i Windows Registry hvor driveren har default indstillingen for hvor mange rækker der læses.

Du kan læse mere her: 33257 - A warning might be returned when you read a Microsoft Excel worksheet with a DATA step or wi...

by SAS Employee OleSteen
on ‎12-02-2014 09:49 AM

Alle ovenstående berører problematikker/løsninger når du har én Excel-fil med mange faneblade.

Hvis du derimod har en folder med flere Excel-filer, er her en macro som kan anvendes til dette formål - bare hvis nogen har det problem Smiley Wink

Jeg har f.eks. i folderen D:\Temp en fil med samme struktur pr. måned, navngivet Januar.xlsx, Februar.xlsx, Marts.xlsx etc.

Med macroen kan jeg nu importere alle filerne i én SAS-tabel.

Macroen har en indbygget hjælpe-facilitet, så du kan kalde den som %ReadFiles(help=y), og du ser et par eksempler i SAS-loggen.

%macro readfiles(path,ext,dlm=,help=N);
     %if %upcase(%substr(&help,1,1)) ^= N %then %do;
          %put NOTE: This macro can be used for importing all files in a directory.;
          %put NOTE- The output will be saved in a table named WORK.FILES.;
          %put NOTE- A new column naming the inputfile will be added.;
          %put;
          %put NOTE- Parameters:;
          %put NOTE-   Path;
          %put NOTE-   Extension (only XLSX,XLS,CSV and TXT allowed);
          %put NOTE-   DLM = (Delimiter - only necessary for extension CSV and TXT);
          %put NOTE-   HELP = (If not N you will get this help);
          %put;
          %put NOTE- Samples:;
          %put WARNING- Import all XLSX files:;
          %put NOTE-    %nrstr(%READFILES(D:\Temp,XLSX));
          %put WARNING- Import all XLS files:;
          %put NOTE-    %nrstr(%READFILES(D:\Temp,XLS));
          %put WARNING- Import all CSV files delimited by semicolon:;
          %put NOTE-    %nrstr(%READFILES(D:\Temp,CSV,DLM=%str%(';')));
          %put WARNING- Import all TXT files delimited by TAB:;
          %put NOTE-    %nrstr(%READFILES(D:\Temp,TXT,DLM=%str%('09'x)));
     %end;
     %else %do;
         options validvarname=any nonotes nosource;
         %let ext = %upcase(&ext);
          %let error = 0;
          /* Verify that extention is allowed */
          %if &ext ^= XLS and
              &ext ^= XLSX and
               &ext ^= CSV and
               &ext ^= TXT %then %do;
               %put ERROR: Only XLS,XLSX,CSV and TXT are allowed extentions;
               %let error = %eval(&error+1);
          %end;
          /* Verify that delimiter is specified for DLM-files */
          %if &error = 1 %then %do;
               %if &dlm = and %substr(&ext,1,3) ^= XLS %then %do;
                    %put ERROR: Delimiter must be specified for this extension;
                    %let error = %eval(&error+1);
               %end;
          %end;
          /* Verify that path is provided */
          %if &path = %then %do;
               %put ERROR: Path must be specified;
               %let error = %eval(&error+1);
          %end;
          %if &error = 0 %then %do;
               /* Delete table files if exist */
              %if %sysfunc(exist(files)) %then %do;
                   proc delete data=files;
                    run;
               %end;
               /* Delete table _temp if exist */
              %if %sysfunc(exist(_temp)) %then %do;
                   proc delete data=_temp;
                    run;
               %end;
               /* Assign fileref */
               %let fileref = path;
               %let rc = %sysfunc(filename(fileref,&path));
               %let id = %sysfunc(dopen(path));
               /* Verify existens of members in folder */
               %if &id = 0 %then %do;
                   %put ERROR: Members in folder &path could not be accessed;
               %end;
               /* Import each member and append the import to table files */
               %else %do i = 1 %to %sysfunc(dnum(&id));
                   %if %upcase(%scan(%sysfunc(dread(&id,&i)),-1)) = &ext %then %do;
                        %put NOTE: Read file: &path.\%sysfunc(dread(&id,&i));
                         proc import file="&path.\%sysfunc(dread(&id,&i))" 
                                 out=_temp replace
                                        dbms=%if %substr(&ext,1,3) = XLS %then %do;
                                             excelcs;
                                        %end;
                                        %else %do;
                                             DLM;
                                             delimiter=&dlm;
                                        %end;
                         run;
                         data _temp;
                           length FromFile $100;
                           FromFile = "&path.\%sysfunc(dread(&id,&i))";
                           set _temp;
                         run;
                         proc append base=files data=_temp force;
                         run;
                    %end;
               %end;
               /* Delete table _temp */
               proc delete data=_temp;
               run;
               /* Close directory and fileref */
               %let id = %sysfunc(dclose(&id));
               %let rc=%sysfunc(filename(filref));
          %end;
          options notes source;
     %end;
%mend;