BookmarkSubscribeRSS Feed
abhityagi
Obsidian | Level 7

Hi Team,

 

I am trying to create given format for 2 million records. Please help me on this. Format is looks like given below:

 

Proc format;

value $urban

'xyqa1'='1'

'qwas2'='2'

'abcd1'='3'

----------

---------

---------

other='00'

;

 

Like that I have 1 million records. Please help me for that how we can create proc format for 1 million records.

 

Thanks,

Abhishek

8 REPLIES 8
Kurt_Bremser
Super User

With that many lookup values, I'd revert to joining/merging. I doubt that you could specify such a format in proc format code, as the length of the necessary value statement will exceed the maximum length of statements that the SAS interpreter can read (32767 bytes). You might try creating a cntlin file from your data.

Shmuel
Garnet | Level 18

Do you have already a file containg urban name and urban code ?

If yes - you can create a "cntl" file to be used by:

     proc format lib=any cntlin=<the cntl dataset created>; run;

 

Example:

 

data sample;
    input u_name $ u_code;
cards;
abc 1
def 2
ghi 3
; run;

data cntl;
 set sample end=eof;
       retain fmtname 'urban'  type 'C' hlo ' ';
       start = u_name; end = start;
       label = left(u_code);
       output;

      if eof then do;
         start = '**OTHER**'; end=start;
        hlo = 'O';
        output;
     end;
run;

proc format lib=work cntlin=cntl;
run;

 

abhityagi
Obsidian | Level 7

Thanks Shumel. Yes I have containing urban name and code.

After that I have a field(ZIP) and with the help of this I want to drive a new fiels postal_code.

 

postal_code=put(ZIP,$u_name);

 

I want to assign postal_code=u_code IF Zip is matching with u_name.

 

Please help.

Shmuel
Garnet | Level 18

As @Kurt_Bremser remrked, you may have memory issues and reach the limits.

 

It will be helpful if you post an example of your input (few/several records),

with all relevant variables (urban name, code,  zip ...),

and the output result you expect to achieve.

 

Then we can propose a more eficient code to get the desired results. 

Kurt_Bremser
Super User

A cntlin dataset for proc format needs to have at least 4 variables:

start - containing the value to be formatted/replaced

label - containing the target value

fmtname - the name of the format to be created

type - either 'C' or 'N', denoting character or numeric format. 'C' when start is character, 'N' when start is numeric.

 

So you need to create the cntlin dataset from your current dataset by renaming two variables to start and label and adding type and fmtname.

KachiM
Rhodochrosite | Level 12

Your problem can be solved by Hash Objects too.

Yet it depends and seeing your sample datset, it can be confirmed.

AlanC
Barite | Level 11

I aksed a similar question a number of years back to a senior birdie at SAS. They said above about 250K records and the value of the format drops off. Not sure how applicable it is today but little should have changed. That is what I always use as a line.

 

Agreed with other poster who said use a hash. 

https://github.com/savian-net
abhityagi
Obsidian | Level 7

Hi Alan.

 

Thanks for reply. I got the solution of this problem. I took all the labels in flat files and take into DATA STEP. We can use CNTLIN option later on for Proc format.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1809 views
  • 6 likes
  • 5 in conversation