topic import excel files in SAS Enterprise Guide

import excel files

Daily1 — Tue, 26 Apr 2022 16:51:04 GMT

i have a dataset in excel file , then how to write format length datatype

this type of variable

i want change this

Re: import excel files

PaigeMiller — Tue, 26 Apr 2022 17:05:15 GMT

Please write more words, that explain the problem in detail, emphasis on "in detail", we don't want brevity.

Re: import excel files

Daily1 — Tue, 26 Apr 2022 17:14:46 GMT

i want to change format, data type (char/num) and length ..........just i want easy code to change this things
/* --------------------------------------------------------------------
Code generated by a SAS task

Generated on Tuesday, April 26, 2022 at 10:11:27 PM
By task: Import Data Wizard

Source file: C:\Users\HP\Downloads\Sample - Superstore.xls
Server: Local File System

Output data: WORK.Sample _ Superstore
Server: SASApp

Note: In preparation for running the following code, the Import
Data wizard has used internal routines to transfer the source data
file from the local file system to SASApp. There is no SAS code
available to represent this action.
-------------------------------------------------------------------- */

/* --------------------------------------------------------------------
This DATA step reads the data values from a temporary text file
created by the Import Data wizard. The values within the temporary
text file were extracted from the Excel source file.
-------------------------------------------------------------------- */

DATA WORK.'Sample _ Superstore'n;
LENGTH
'Row ID'n 8
'Order ID'n $ 14
'Order Date'n 8
'Ship Date'n 8
'Ship Mode'n $ 14
'Customer ID'n $ 8
'Customer Name'n $ 22
Segment $ 11
Country $ 13
City $ 17
State $ 20
'Postal Code'n $ 5
Region $ 7
'Product ID'n $ 15
Category $ 15
'Sub-Category'n $ 11
'Product Name'n $ 127
Sales 8
Quantity 8
Discount 8
Profit 8 ;
FORMAT
'Row ID'n BEST12.
'Order ID'n $CHAR14.
'Order Date'n DATE9.
'Ship Date'n DATE9.
'Ship Mode'n $CHAR14.
'Customer ID'n $CHAR8.
'Customer Name'n $CHAR22.
Segment $CHAR11.
Country $CHAR13.
City $CHAR17.
State $CHAR20.
'Postal Code'n $CHAR5.
Region $CHAR7.
'Product ID'n $CHAR15.
Category $CHAR15.
'Sub-Category'n $CHAR11.
'Product Name'n $CHAR127.
Sales BEST12.
Quantity BEST12.
Discount BEST12.
Profit BEST12. ;
INFORMAT
'Row ID'n BEST12.
'Order ID'n $CHAR14.
'Order Date'n DATE9.
'Ship Date'n DATE9.
'Ship Mode'n $CHAR14.
'Customer ID'n $CHAR8.
'Customer Name'n $CHAR22.
Segment $CHAR11.
Country $CHAR13.
City $CHAR17.
State $CHAR20.
'Postal Code'n $CHAR5.
Region $CHAR7.
'Product ID'n $CHAR15.
Category $CHAR15.
'Sub-Category'n $CHAR11.
'Product Name'n $CHAR127.
Sales BEST12.
Quantity BEST12.
Discount BEST12.
Profit BEST12. ;
INFILE '/saswork/SAS_workA36A00006151_odaws01-apse1.oda.sas.com/#LN00015'
LRECL=307
ENCODING="UTF-8"
TERMSTR=CRLF
DLM='7F'x
MISSOVER
DSD ;
INPUT
'Row ID'n : BEST32.
'Order ID'n : $CHAR14.
'Order Date'n : BEST32.
'Ship Date'n : BEST32.
'Ship Mode'n : $CHAR14.
'Customer ID'n : $CHAR8.
'Customer Name'n : $CHAR22.
Segment : $CHAR11.
Country : $CHAR13.
City : $CHAR17.
State : $CHAR20.
'Postal Code'n : $CHAR5.
Region : $CHAR7.
'Product ID'n : $CHAR15.
Category : $CHAR15.
'Sub-Category'n : $CHAR11.
'Product Name'n : $CHAR127.
Sales : BEST32.
Quantity : BEST32.
Discount : BEST32.
Profit : BEST32. ;
RUN;

Re: import excel files

PaigeMiller — Tue, 26 Apr 2022 17:20:00 GMT

You can just change the code used to read in the Excel file. SAS has provided you with this code.

Re: import excel files

Tom — Tue, 26 Apr 2022 17:28:24 GMT

So from the latest post is looks like.

1) You are using Enterprise Guide.

2) You are using the Import wizard of Enterprise Guide to read a file that is accessible of the machine where you are running Enterprise Guide (which could be a completely different machine that where the SAS code that Enterprise Guide generates runs).

3) You can an old style XLS file instead of a more modern XLSX file.

What the Enterprise Guide wizard has done is read in that XLS file on your Windows machine and converted it to a text file. It then uploaded that file and generated and run the data step you showed the log messages about.

Unfortunately I do not know if that Wizard has any way for you to tell it how to write the code to read the text file it generated. Or if has anything you can control to change how it generates the text file from the XLS file.

With normal SAS code there is very little you can do to control how PROC IMPORT will define the variables created from an XLS or XLSX file.

So most likely you will need to either:

1) Add an additional step after the "import" to make a new version of the file with the data types you want (note in SAS the word FORMAT means the way that a variable is DISPLAYED, not how it is STORED).

2) Add a step your PC to convert the sheet from the XLS file into a text file. Then upload that file to your SAS server and write your own data step to read it.

Re: import excel files

Daily1 — Tue, 26 Apr 2022 17:32:50 GMT

First Format all the variable

Name Type Length Format Informat Label
no Numeric 8 BEST12. BEST12.
YEAR Numeric 8 BEST12. BEST12.
PSL Character 5 $CHAR5. $CHAR5.
SCHEME Numeric 8 BEST12. BEST12.
INDUSTRY Character 4 $CHAR4. $CHAR4.
CD Character 5 $CHAR5. $CHAR5.
STATE Numeric 8 BEST12. BEST12.
District Numeric 8 BEST12. BEST12.
SECTOR Numeric 8 BEST12. BEST12.

just i want change this type format

Name Type Length Format Informat Label
no Numeric 8 6 BEST12. SCH_DES_NO
YEAR Character 28 $28. $28. YEAR
PSL Character 5 $5. $CHAR5.
SCHEME Numeric 8 BEST12. BEST12. SCH_CODE
INDUSTRY Character 40 $4. $40. NIC_04_4DIGIT
CD Character 40 $5. $40. NIC_04_5DIGIT
STATE Numeric 8 BEST12. BEST12. STATE_CODE
District Numeric 8 2
SECTOR Numeric 8 BEST12. BEST12. SECTOR