BookmarkSubscribeRSS Feed
Angel_Saenz
Quartz | Level 8

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

 

3 REPLIES 3
ballardw
Super User

proc sql;

   create table want as

   select distinct Idnumber, CostumerNumber

   from YourDataSet

   where not missing(CostumerNumber);

quit;

 

Not claiming to be the most efficient.

 

Angel_Saenz
Quartz | Level 8

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

ballardw
Super User

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.

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
  • 3 replies
  • 592 views
  • 0 likes
  • 2 in conversation