DATA Step, Macro, Functions and more

Obtain to IDnumber unique and CostumerNumber

Reply
Contributor
Posts: 39

Obtain to IDnumber unique and CostumerNumber

How can I obtain IDnumber unique and CostumerNumber if data set is like:

Te problem is CostumerNumbe(num) in some information months is missing and in others not.

The data set has 600 milllons of obs.

Idnumber(char) CostumerNumbe(num) INFORMATION DATE
100                             .                              31May2013
100                             1234                       30Jun2013
100                             .                              31Jul2013
100                             1234                       31Aug2013
100                             .                              30Sep2013
100                             .                              31Oct2013
100                             1234                       30Nov2013
100                             1234                       31Dec2013
100                             1234                       31Jan2014
100                             .                              28Feb2014
101                             .                              31May2013
101                             6754                       30Jun2013
101                             6754                       31Jul2013
101                             .                              31Aug2013
101                             6754                       30Sep2013
101                             .                              31Oct2013
101                             6754                       30Nov2013
101                             .                              31Dec2013
101                             6754                       31Jan2014
101                             .                              28Feb2014

 

The result I want is:

 Idnumber(char) CostumerNumbe(num)
100                                 1234
101                                 6754

 

Super User
Posts: 10,521

Re: Obtain to IDnumber unique and CostumerNumber

proc sql;

   create table want as

   select distinct Idnumber, CostumerNumber

   from YourDataSet

   where not missing(CostumerNumber);

quit;

 

Not claiming to be the most efficient.

 

Contributor
Posts: 39

Re: Obtain to IDnumber unique and CostumerNumber

Thank you Ballardw, do you know other way using just sas code? not using proc sql

Super User
Posts: 10,521

Re: Obtain to IDnumber unique and CostumerNumber

Proc sql is BASE SAS code, just one procedure among many.

 

Are you requesting a data step solution? The other relatively easy approaches involve Proc Summary or Proc freq but end up with summary variables that get dropped from the result data set.

 

proc freq data=yourdata noprint;

    where not missing(CostomerNumber);

    tables Idnumber* CostumerNumber / list out=want(drop=count percent);

run;

for instance.

Ask a Question
Discussion stats
  • 3 replies
  • 85 views
  • 0 likes
  • 2 in conversation