How to create SAS7bdat datasets in UNIX environment

[librasonali]

Hi All,

I want to create a newcipcru&DT..SAS7bdat  which will be a new dataset with few newly fields . My main motive is to create an output dataset ( all) with  combined fields data. 

Right now situation is I am having 1000+ records in cipcru&DT. but as I cannot create 1000+  so created just 2 with same value in primary key field CRS_ACCT_NBR so that they can concatenate. 

But I am not getting desrired result :

desired output : 

CRS_ACCT_NBR	CRS_CUSTOMER_TYPE	CRS_LINE_OF_BUSINESS	CRS_REF_NUM	CRS_APPLICATION_ID	CRS_BO_ATSTN_DATE	CRS_BO_EXEMPTION_IND	CRS_BO_EXEMPTION_SRC	CRS_STOCK_EXCHANGE	CRS_STOCK_SYMBOL
123	P001	CRS	171080000317	171080000317	01052020	Y	Source	ABC	N

my code : 

%LET DT=050521;
/*%LET DT1=042521;DCF */
%let DT2=20210428;
/*%let DT3=20210505 DCF */



libname s9 "/stage_1/download/backup/adhoc/kyccrs/&DT./thd_cipcru";
/*libname db ".";*/

/* CREATING TEMP DATASETS */

Data newcipcru&DT.;
input 
@1 CRS_BO_ATSTN_DATE $10.
@2 CRS_BO_EXEMPTION_IND $3.
@3 CRS_BO_EXEMPTION_SRC $200.
@4 CRS_STOCK_EXCHANGE $200.
@5 CRS_STOCK_SYMBOL $200.
@6 CRS_ACCT_NBR $20  ;

/*format CRS_BO_ATSTN_DATE mmddyyyy10.;*/
datalines;
01052020 Y source ABC N 4269390002452632
02052021 N        CD    4269390002452657
;

run;

data all;
set s9.cipcru&DT. newcipcru&DT.;
run;


proc contents data=newcipcru&DT. directory;
run; 

proc export data =all
outfile="/etl/home/rrtqarun/data/aml/Datasets/cardholder/sb56634/cda_thd_cipcrup_&DT2..csv"
dbms=csv
replace;
delimiter= '|';
run;

[Tom]

I am note sure I understand the question. The normal way to make a SAS dataset is to run a data step.

libname out 'some unix path';
data out.newcipcru&DT. ;
  ... rest of data step code to create the dataset ... 
run;

[ballardw]

When you show a "desired output" you should also show what the actual input is.

First problem I see is this input statement.

input 
@1 CRS_BO_ATSTN_DATE $10.
@2 CRS_BO_EXEMPTION_IND $3.
@3 CRS_BO_EXEMPTION_SRC $200.
@4 CRS_STOCK_EXCHANGE $200.
@5 CRS_STOCK_SYMBOL $200.
@6 CRS_ACCT_NBR $20  ;   

 You are telling SAS to start reading CRS_BO_EXEMPTION_IND at column 2 (that is what @2 means), which would be the second character of the CRS_BO_ATSTN_DATE, then the third variable starts reading at the 3rd character. Down to your CRS_ACCT_NBR (with incomplete informat causing an issue) starting with column 6 in the middle of the date. So your code does not read the variables correctly.

Compounding this you have informats that will force reading many columns and overlapping for the example data.

So your first step might be to show what your expectation is for the first data step as you get invalid data messages in the log (you do read the log don't you?).

If this is supposed to be reading an external file then an example of the file would be in order.

Copy 5 or 10 lines from the source with a text editor, open a text box on the forum with the </> icon and paste the copied text. The text box is important because the forum message windows will reformat text.

Why are you reading inline data with a $200. informat when you show only a few characters that need to be read?

This may be closer to what you need for that:

Data junk;
input 
 CRS_BO_ATSTN_DATE    :mmddyy10.
 CRS_BO_EXEMPTION_IND :$3.
 CRS_BO_EXEMPTION_SRC :$200.
 CRS_STOCK_EXCHANGE   :$200.
 CRS_STOCK_SYMBOL     :$200.
 CRS_ACCT_NBR         :$20.  ;
format   CRS_BO_ATSTN_DATE mmddyy10. ;
/*format CRS_BO_ATSTN_DATE mmddyyyy10.;*/
datalines;
01052020 Y source ABC N 4269390002452632
02052021 N .      CD  .  4269390002452657
;

When the informat is preceded with a : then it reads the next value up to the length specified.

Placing the . in the datalines means that there is a "next value" but it is treated as missing by default so you don't mix fixed column reading when list is more appropriate.

Good idea to read dates as dates not character.

[librasonali]

hi , I here in the code incorrectly wrote @1 and @2 but in my genuine code it was odd number positioning. apologies!! 

I took date field as character because in the different code for my project I have to convert that character field into Date. but in my updated code i have taken it as Date ( character is also working fine ) 

Please find below my updated code ;

now I can see my desired output. Thank -you all 

%LET DT=050521;
/*%LET DT1=042521;DCF */
%let DT2=20210505;
/*%let DT3=20210505 DCF */



libname s9  "/stage_1/download/backup/adhoc/kyccrs/&DT./thd_cipcru";

libname out "/etl/home/rrtqarun/data/aml/Datasets/cardholder/sb56634/";

/* CREATING TEMP DATASETS */

Data out.newcipcru&DT.;
input 
 CRS_BO_ATSTN_DATE    :mmddyy10.
 CRS_BO_EXEMPTION_IND :$3.
 CRS_BO_EXEMPTION_SRC :$200.
 CRS_STOCK_EXCHANGE   :$200.
 CRS_STOCK_SYMBOL     :$200.
 CRS_ACCT_NBR         :$20.  ;
format   CRS_BO_ATSTN_DATE mmddyy10. ;
/*format CRS_BO_ATSTN_DATE mmddyyyy10.;*/
datalines;
01052020 Y source ABC N 4269390002452632
02052021 N .      CD  . 4269390002452657
;
run;


proc sort data=out.newcipcru&DT.;
        by CRS_ACCT_NBR;
run;

data all;
merge s9.cipcru&DT.(in=a) out.newcipcru&DT(in=b);
by CRS_ACCT_NBR;
if a and b;
run;

/*data all;
set s9.cipcru&DT. out.newcipcru&DT.;
run;
*/

proc contents data=out.newcipcru&DT. directory;
run; 

proc export data =all
outfile="/etl/home/rrtqarun/data/aml/Datasets/cardholder/sb56634/cda_thd_cipcru_&DT2..csv"
dbms=csv
replace;
delimiter= '|';
run;