BookmarkSubscribeRSS Feed
R_Win
Calcite | Level 5

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

8 REPLIES 8
art297
Opal | Level 21

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;

    R_Win
    Calcite | Level 5

    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

    Ksharp
    Super User

    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

    art297
    Opal | Level 21

    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;

    Ksharp
    Super User

    Hi. Art.T

    I know your code is correct.

    Ksharp

    MikeZdeb
    Rhodochrosite | Level 12

    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


    Ksharp
    Super User

    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

    MikeZdeb
    Rhodochrosite | Level 12

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

    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!

    What is Bayesian Analysis?

    Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

    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
    • 929 views
    • 4 likes
    • 4 in conversation