BookmarkSubscribeRSS Feed
don21
Quartz | Level 8

Hello,

Am in middle of some project and am trying to find a logic for the below. Please advise.

Dataset

col1  col2  col3

123 

123  001

123  001

456

456  101

123

123  033

123  101

456

456  001

456  001

there are set of numbers in col1 and some 3 dig codes in col2 which starts with a blank value. I need the unique set of codes(col2) which are either repetitive or non repetitve. all the unique sets needs to be printed either into new variable or a new dataset. Please advise.

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

Not sure I am quite following you.  Numbers cannot have a blank value?  For duplicates, use:

proc sort data=have out=want (keep=col2) nodupkey;

     by col2;

run;

ballardw
Super User

Please provide what you would expect the output to look like as there are several ways to interpret your written requirement.

don21
Quartz | Level 8

Hello,

The dataset I have looks like:

NumberSet_of_codes
123456.
123456001
123456001
456789.
456789022
456789001
456789339
456789010
456789022
456789110
456789110
789102.
789102111
789102110
789102022
123456.
123456022
123456001
123456339
123456010
123456022
123456110
123456110
456789.
456789001
456789001

Expected Output is :

NumberSet_of_codesunique_set_of_codesCount
123456..3
123456001001
123456001001
456789..
456789022022
456789001001
456789339339
456789010010
456789022022
456789110110
456789110110
789102..
789102111111
789102110110
789102022022
123456.
123456022
123456001
123456339
123456010
123456022
123456110
123456110
456789.
456789001
456789001

Every sequence of code starts with a blank. Need to identify such unique sets based on card numbers in col1 and the unique set count.

Many thanks in advance.

dcruik
Lapis Lazuli | Level 10

In addition to RW9's response using the nodupkey in the sort procedure you can also use the select distinct in a sql procedure:

proc sql;

create table want as

select unique col2

from have;

quit;

don21
Quartz | Level 8

Hello,

sorry for the confusion, Below is the detailed explanation:

NumberSet_of_codes
123456.
123456001
123456022
456789.
456789022
456789022
456789339
456789068
456789110
456789001
123456.
123456010
123456022
123456339
123456068
3598754.
3598754001
3598754022
456789.
456789339
3598754.
3598754339

Above table contains number and some set of codes(starts with missing values). one set of code is for one card number (number). I need to find the unique set of codes in that column (set of codes) as below:

expected:

unique_set_of_codesUnique_Count
.4
001
022
.
022
022
339
068
110
001
.
010
022
339
068
.
339

Thanks in advance.

don21
Quartz | Level 8

Hello,

The dataset that I have looks like:

NumberSet_of_codes
123456.
123456001
123456001
456789.
456789022
456789001
456789339
456789010
456789022
456789110
456789110
789102.
789102111
789102110
789102022
123456.
123456022
123456001
123456339
123456010
123456022
123456110
123456110
456789.
456789001
456789001

Expected Output is :

NumberSet_of_codesunique_set_of_codesCount
123456..3
123456001001
123456001001
456789..
456789022022
456789001001
456789339339
456789010010
456789022022
456789110110
456789110110
789102..
789102111111
789102110110
789102022022
123456.
123456022
123456001
123456339
123456010
123456022
123456110
123456110
456789.
456789001
456789001

Every sequence of code starts with a blank. Need to identify such unique sets based on card numbers in col1 and the unique set count.

Note: There might be double missing values in sequence in between the data (col2) instead of single missing value.

Many thanks in advance.

Patrick
Opal | Level 21

Your expected output has the same number of rows like your "have" data. The values for "Set_of_codes" are identical to "unique_set_of_codes". I don't understand what you want to do here. I also don't understand what you count of 3 is based on.

don21
Quartz | Level 8

I need only col3 and col4 in the output. the count 3 is nothing but the count of unique set of codes.

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
  • 1084 views
  • 0 likes
  • 5 in conversation