Help using Base SAS procedures

Reg Generating Seq no

Reply
Regular Contributor
Posts: 229

Reg Generating Seq no

Hi i am having a data set city i wnat to give no if the city is  the same and should produce 1 new number if it is blank

data temp;

input city $;

cards;

hyb

vja

nlg

    

mum

pune

pune

     

hyb

     

vja

  

triven

run;

output: 

city      no

hyb      1

vja        2

nlg       3

           4

mum    5

pune    6

pune    6

           7

hyb      1

            8 

vja       2

           9

triven   10

PROC Star
Posts: 7,471

Reg Generating Seq no

As usual, there are a number of ways to accomplish what you want.  e.g.:

data temp (drop=citie: number);

  array cities(99999) $;

  infile cards truncover;

  input city $;

  retain cities;

  if city ne "" then do;

    if whichc(city,of cities

  • ) gt 0 then
  •      no=whichc(city,of cities

  • );
  •     else do;

          number+1;

          cities(number)=city;

          no=number;

        end;

      end;

      else do;

        number+1;

        no=number;

      end;

      cards;

    hyb

    vja

    nlg

       

    mum

    pune

    pune

        

    hyb

        

    vja

     

    triven

    ;

    run;

    Regular Contributor
    Posts: 229

    Re: Reg Generating Seq no

    Thqs it worked but when i added set statement for the blank nubers the no is not generatring

    data temp1 (drop=citie: number);

    set temp;

      array cities(99999) $;

      retain cities;

      if city ne "" then do;

        if whichc(city,of cities

  • ) gt 0 then
  •      no=whichc(city,of cities

  • );
  •     else do;

          number+1;

          cities(number)=city;

          no=number;

        end;

      end;

      else do;

        number+1;

        no=number;

      end;

    run;

    proc print;

    run;

    But i want the no for blank values also..

                                                            

    output:

                                                            Obs    city      no

                                                             1     hyb        1
                                                             2     vja        2
                                                             3     nlg        3
                                                             4     mum        4
                                                             5     pune       5
                                                             6     pune       5
                                                             7     hyb        1
                                                             8     vja        2
                                                             9     triven     6

    Super User
    Posts: 10,023

    Re: Reg Generating Seq no

    For your situation. Hash table is the best choice.

    How about:

    data temp;
    infile datalines truncover;
    input city $ ;
    datalines;
    hyb
    vja
    nlg
        
    mum
    pune
    pune
         
    hyb
         
    vja
      
    triven
    sdsd
    ;
    run;
    data want(drop=rc x _count );
     set temp;
     retain count _count;
     if _n_ eq 1 then do;
                         declare hash ha(hashexp:10);
                         ha.definekey('city');
                         ha.definedata('_count');
                         ha.definedone();
                      end;
    
     if not missing(city) then do;rc=ha.find();count=_count;end;
     if rc ne 0 and not missing(city) then do;x+1;count=x;_count=x;ha.add(); end;
     if missing(city) and not missing(lag(city)) then do;x+1; count=x;end;
    run;
    

    Ksharp

    PROC Star
    Posts: 7,471

    Re: Reg Generating Seq no

    data temp1 (drop=citie: number);

      set temp;

      array cities(99999) $;

      retain cities;

      if city eq "" then do;

        number+1;

        no=number;

      end;

      else if city ne "" then do;

        if whichc(city,of cities

  • ) gt 0 then
  •      no=whichc(city,of cities

  • );
  •     else do;

          number+1;

          cities(number)=city;

          no=number;

        end;

      end;

      else do;

        number+1;

        no=number;

      end;

    run;

    Super User
    Posts: 10,023

    Re: Reg Generating Seq no

    Hi. Art.T

    I know your code is correct.

    Ksharp

    Valued Guide
    Posts: 765

    Re: Reg Generating Seq no

    Hi ... another idea with just the CATX and FINDW functions ...

    data temp;

    input city $8. ;

    cards;

    hyb

    vja

    nlg

    mum

    pune

    pune

    hyb

    vja

    triven

    run;

    data x;

    length x $1000;

    retain x;

    set temp;

    select;

       when (missing(city))        do; x = catx(' ',x,'miss'); nc+1; count=nc; end;

       when (^findw(x,trim(city))) do; x = catx(' ',x,city); nc+1; count=nc; end;

       otherwise                   count = findw(x,trim(city),' ','e');

    end;

    keep city count;

    run;

    proc print data=x noobs;

    run;

    city      count

    hyb          1

    vja          2

    nlg          3

                 4

    mum          5

    pune         6

    pune         6

                 7

    hyb          1

                 8

    vja          2

                 9

    triven      10


    Super User
    Posts: 10,023

    Re: Reg Generating Seq no

    Hi. If Op has lots of cities, you can not do it .  because  x can not hold all the cities, the max length of x is 32767.

    Ksharp

    Valued Guide
    Posts: 765

    Re: Reg Generating Seq no

    Hi ... there's always "y" and "z" once "x" reaches a certain size ... or your hash object.

    Ask a Question
    Discussion stats
    • 8 replies
    • 235 views
    • 4 likes
    • 4 in conversation