Hi Everyone,
when I run this code in SAS i get the results shown with the given format
data Color;
input Region Eyes $ Hair $ Count prop @@;
label Eyes ='Eye Color'
Hair ='Hair Color'
Region='Geographic Region';
format prop percent10.2;
datalines;
1 blue fair 23 .4 1 blue red 7 .4 1 blue medium 24 .4
1 blue dark 11 .3 1 green fair 19 .3 1 green red 7 .3
1 green medium 18 .3 1 green dark 14 .3 1 brown fair 34 .3
1 brown red 5 .7 1 brown medium 41 .7 1 brown dark 40 .7
1 brown black 3 .8 2 blue fair 46 .8 2 blue red 21 .8
2 blue medium 44 .9 2 blue dark 40 .9 2 blue black 6 .9
2 green fair 50 .11 2 green red 31 .11 2 green medium 37 .11
2 green dark 23 .35 2 brown fair 56 .35 2 brown red 42 .35
2 brown medium 53 .200 2 brown dark 54 .200 2 brown black 13 .200
;
However, when bulkloading it to a DB2-Table (WORKD is the SAS-libref of a DB2-Schema)
data WORKD.Color (BULKLOAD=YES BL_METHOD=CLILOAD BL_RECOVERABLE=NO BL_LOAD_REPLACE=yes) ;
set work.Color;
run;
I get the following notes in the log:
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: There were 27 observations read from the data set WORK.COLOR.
NOTE: The data set WORKD.COLOR has 27 observations and 5 variables.
NOTE:
Results for CLI LOAD operation:
27 rows loaded.
0 rows skipped.
0 rows rejected.
0 rows deleted as duplicates.
27 rows committed.NOTE: DATA statement used (Total process time):
real time 3.35 seconds
cpu time 0.01 seconds
and consequently, the result looks like this:
It may seem as the dumbest question of all, but I simply want to know:
why can DB2 not handle the labels, formats, lengths???
Cheers,
FK1
@FK1 :
Labels and formats, the way they exist in SAS, aren't DB2 concepts, and so they are not applied and/or stored in its system tables.
When you move data to DB2, you accept its rules.
The lengths of the SAS character variables are most likely mimicked by DB2 by creating CHAR(N) type columns of the same lengths as imported from SAS, though it may depend on a number of factors.
How the real binary SAS numbers (system length 😎 are stored in DB2 is up to its discretion. Chances are, they're stored as DECIMAL or REAL data type; but whether they will have the same system lengths as in SAS is no guarantee. But I don't think it's a reason to get disturbed because they are stored with the full precision they come with from SAS.
Kind regards
Paul D.
@FK1 :
Labels and formats, the way they exist in SAS, aren't DB2 concepts, and so they are not applied and/or stored in its system tables.
When you move data to DB2, you accept its rules.
The lengths of the SAS character variables are most likely mimicked by DB2 by creating CHAR(N) type columns of the same lengths as imported from SAS, though it may depend on a number of factors.
How the real binary SAS numbers (system length 😎 are stored in DB2 is up to its discretion. Chances are, they're stored as DECIMAL or REAL data type; but whether they will have the same system lengths as in SAS is no guarantee. But I don't think it's a reason to get disturbed because they are stored with the full precision they come with from SAS.
Kind regards
Paul D.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.