Using the cntlin data set

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

Using the cntlin data set

Hi friends,

Sure am in need of making up a temporary dataset based on a CSV file to look up some relevant entries using the FORMAT PROC. Although have been successful in numerical data, need some help in respect of Character entries. Following is the mode of success in having days being classified in terms of buckets with a csv file reflecting:

STARTENDSEXCLEEXCLHLOFMTNAMETYPEBUCKETLABEL
LOW29YNLBUCKETN1Bkt X (1-29 DPD)
3059NN BUCKETN2Bkt 1 (30-59 DPD)
6089NN BUCKETN3Bkt 2 (60-89 DPD)
90119NN BUCKETN4Bkt 3 (90-119 DPD)
120149NN BUCKETN5Bkt 4 (120-149 DPD)
150179NN BUCKETN6Bkt 5 (150-179 DPD)
180209NN BUCKETN7Bkt 6 (180-209 DPD)
210239NN BUCKETN8Bkt 7 (210-239 DPD)
240269NN BUCKETN9Bkt 8 (240-269 DPD)
270299NN BUCKETN10Bkt 9 (270-299 DPD)
300329NN BUCKETN11Bkt 10 (300-329 DPD)
330364NN BUCKETN12Bkt 11 (330-364 DPD)
365HIGHNNHBUCKETN13Bkt 12 (365+ DPD)

and using the same in a PROC SQL as:

PROC FORMAT Cntlin = FAQ_PDs.Bucket_Format; QUIT;

PROC SQL;

  CREATE TABLE ABC AS SELECT *, PUT(DPD, BUCKET.) AS DAYS_BUCKET;

QUIT;

However, while I have tried out the same technique in a Character dataset:

FMTNAMESTARTENDSEXCLEEXCLTYPEPRODLABEL
$PRODPast Dues on Account OverdraftsPast Dues on Account OverdraftsNNC1RF
$PRODImport related loansImport related loansNNC2TRADE
$PRODTerm FinanceTerm FinanceNNC3TF
$PRODExport RefinanceExport RefinanceNNC4TRADE
$PRODLC Import Usance NegotiationLC Import Usance NegotiationNNC5TRADE
$PRODInland LC Import AcceptanceInland LC Import AcceptanceNNC6TRADE
$PRODInland LC Import Usance NegotiationInland LC Import Usance NegotiationNNC7TRADE
$PRODExport Related LoansExport Related LoansNNC8TRADE
$PRODCASH CREDIT PLEDGECASH CREDIT PLEDGENNC9RF
$PRODImport Contract UsanceImport Contract UsanceNNC10TRADE
$PRODDeferred Mup ReceivableDeferred Mup ReceivableNNC11TF
$PRODGerman loansGerman loansNNC12TF
$PRODLocal Exp Usance Nego(Uncf&Trf)Local Exp Usance Nego(Uncf&Trf)NNC13

TRADE

and using the same in a similar statement as:

PROC FORMAT Cntlin = FAQ_PDs.Prod_Format; QUIT;

PROC SQL;

  CREATE TABLE ABC AS SELECT *, PUT(PROD, PROD.) AS PROD_TYPE FROM FAQ_PDs.PD_20131001;

QUIT;

haven't been successful in returning the TF, RF or Trade for such products listed in Start and End as suggested in the article: http://analytics.ncsu.edu/sesug/2007/TU06.pdf

Please see if any of you experts could help in this regard.

Thanx in advance.


Accepted Solutions
Solution
‎10-06-2013 09:10 PM
Respected Advisor
Posts: 4,173

Re: Using the cntlin data set

Posted in reply to FarazA_Qureshi

In the code "PUT(PROD, PROD.) AS PROD_TYPE" you are applying the format on column "PROD" which has values like 1,2,3,...

In the source data set used to build the format the START and END values are things like "Import related loans". So this can't work.

Is your column "PROD" character or numeric? The way you use the format "PROD." SAS expects it to be a numeric format - but in your format source you define the format as being character.

For PROD being character you have to call the format as follows: "PUT(PROD, $PROD.) AS PROD_TYPE"

Below some code which might give you the idea:

libname FAQ_PDs (work);

data  FAQ_PDs.Prod_Format;
  infile datalines truncover dlm=',' dsd;
  input FMTNAME $ (START END) (:$35.) (SEXCL EEXCL TYPE) (:$1.) PROD :$2. LABEL :$35.;

  /* re-assign values for Start and End as required */
  Start=PROD;
  End=PROD;
  datalines;
$PROD,Past Dues on Account Overdrafts,Past Dues on Account Overdrafts,N,N,C,1,RF
$PROD,Import related loans,Import related loans,N,N,C,2,TRADE,
$PROD,Term Finance,Term Finance,N,N,C,3,TF
$PROD,Export Refinance,Export Refinance,N,N,C,4,TRADE,
$PROD,LC Import Usance Negotiation,LC Import Usance Negotiation,N,N,C,5,TRADE
$PROD,Inland LC Import Acceptance,Inland LC Import Acceptance,N,N,C,6,TRADE
$PROD,Inland LC Import Usance Negotiation,Inland LC Import Usance Negotiation,N,N,C,7,TRADE
$PROD,Export Related Loans,Export Related Loans,N,N,C,8,TRADE
$PROD,CASH CREDIT PLEDGE,CASH CREDIT PLEDGE,N,N,C,9,RF
$PROD,Import Contract Usance,Import Contract Usance,N,N,C,10,TRADE
$PROD,Deferred Mup Receivable,Deferred Mup Receivable,N,N,C,11,TF
$PROD,German loans,German loans,N,N,C,12,TF
$PROD,Local Exp Usance Nego(Uncf&Trf),Local Exp Usance Nego(Uncf&Trf),N,N,C,13,TRADE
;
run;

PROC FORMAT Cntlin = FAQ_PDs.Prod_Format;
run;quit;

/* create some sample data */
data FAQ_PDs.PD_20131001;

  Row+1;

  set FAQ_PDs.Prod_Format(keep=Start rename=(start=prod)) end=last;

  output;

  if last then do; prod='99'; output; end;

run;

PROC SQL;
  CREATE TABLE ABC AS SELECT *, PUT(PROD, $PROD.) AS PROD_TYPE FROM FAQ_PDs.PD_20131001;
QUIT;

View solution in original post


All Replies
Solution
‎10-06-2013 09:10 PM
Respected Advisor
Posts: 4,173

Re: Using the cntlin data set

Posted in reply to FarazA_Qureshi

In the code "PUT(PROD, PROD.) AS PROD_TYPE" you are applying the format on column "PROD" which has values like 1,2,3,...

In the source data set used to build the format the START and END values are things like "Import related loans". So this can't work.

Is your column "PROD" character or numeric? The way you use the format "PROD." SAS expects it to be a numeric format - but in your format source you define the format as being character.

For PROD being character you have to call the format as follows: "PUT(PROD, $PROD.) AS PROD_TYPE"

Below some code which might give you the idea:

libname FAQ_PDs (work);

data  FAQ_PDs.Prod_Format;
  infile datalines truncover dlm=',' dsd;
  input FMTNAME $ (START END) (:$35.) (SEXCL EEXCL TYPE) (:$1.) PROD :$2. LABEL :$35.;

  /* re-assign values for Start and End as required */
  Start=PROD;
  End=PROD;
  datalines;
$PROD,Past Dues on Account Overdrafts,Past Dues on Account Overdrafts,N,N,C,1,RF
$PROD,Import related loans,Import related loans,N,N,C,2,TRADE,
$PROD,Term Finance,Term Finance,N,N,C,3,TF
$PROD,Export Refinance,Export Refinance,N,N,C,4,TRADE,
$PROD,LC Import Usance Negotiation,LC Import Usance Negotiation,N,N,C,5,TRADE
$PROD,Inland LC Import Acceptance,Inland LC Import Acceptance,N,N,C,6,TRADE
$PROD,Inland LC Import Usance Negotiation,Inland LC Import Usance Negotiation,N,N,C,7,TRADE
$PROD,Export Related Loans,Export Related Loans,N,N,C,8,TRADE
$PROD,CASH CREDIT PLEDGE,CASH CREDIT PLEDGE,N,N,C,9,RF
$PROD,Import Contract Usance,Import Contract Usance,N,N,C,10,TRADE
$PROD,Deferred Mup Receivable,Deferred Mup Receivable,N,N,C,11,TF
$PROD,German loans,German loans,N,N,C,12,TF
$PROD,Local Exp Usance Nego(Uncf&Trf),Local Exp Usance Nego(Uncf&Trf),N,N,C,13,TRADE
;
run;

PROC FORMAT Cntlin = FAQ_PDs.Prod_Format;
run;quit;

/* create some sample data */
data FAQ_PDs.PD_20131001;

  Row+1;

  set FAQ_PDs.Prod_Format(keep=Start rename=(start=prod)) end=last;

  output;

  if last then do; prod='99'; output; end;

run;

PROC SQL;
  CREATE TABLE ABC AS SELECT *, PUT(PROD, $PROD.) AS PROD_TYPE FROM FAQ_PDs.PD_20131001;
QUIT;

Contributor
Posts: 51

Re: Using the cntlin data set

Sorry for replying late Pat!

Sure was a busy day at office. However found your solution to be perfect! No doubt, it was lack of the $ sign.

Thanx again buddy.

Trusted Advisor
Posts: 1,137

Re: Using the cntlin data set

Posted in reply to FarazA_Qureshi

Hi, I believe you missed to used the appropriate format name, the format name in the as per the dataset is fmtname=$prod, however in use you have mentioned as PUT(PROD, PROD.)  it should be PUT(PROD, $PROD.)

Thanks,

Jagadish

Thanks,
Jag
Contributor
Posts: 44

Re: Using the cntlin data set

Posted in reply to Jagadishkatam

Hi,


1.   we are inputting dataset contains(fmtname, start,label )to cntlin of format proc . How sas knows type of format to be created(invalue or value).

2.  if fmtname begins with $, it is a character format otherwise numeric format ,can u confirm this same.

      note: some books type='c' to indicate character format, some books mentioned fmtname begins with $ (without mentioning type varible) .

3. when I see a output window cotains a list of formats has the below, what does that mean?

INFORMAT NAME: @EXP1946FMT LENGTH: 1                   ‚

  ‚   MIN LENGTH:   1  MAX LENGTH:  40  DEFAULT LENGTH   1  FUZZ:        0   ‚

thanks in advace.

Super Contributor
Posts: 644

Re: Using the cntlin data set

Use the type variable to indicate which (does not require $ in name)

C = character format

D = character informat

N = numeric format

I  = numeric informat

Richard

Contributor
Posts: 44

Re: Using the cntlin data set

Posted in reply to RichardinOz

hi,

thanks for the reply.

could you please go through my question again and reply

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 408 views
  • 2 likes
  • 5 in conversation