topic Re: When I proc transpose my data by group1 group2, why I get many blanks values? in SAS Programming

When I proc transpose my data by group1 group2, why I get many blanks values?

sbopster — Sun, 23 May 2021 00:54:03 GMT

data have;
input ID 	Card 	SCORE $;
cards;
123 	1111 	AE 	
123 	1111 	AB 
123 	1113 	AE 
456 	2221 	AS 
456 	2221 	BF 
456 	2221 	BF 
456 	2222 	DC 	
456 	2222 	BE 
456 	2222 	DL 
456 	2222 	BF 
456 	2222 	DC 	
456 	2222 	BE 
456 	2222 	DE 			
;
run;

PROC TRANSPOSE DATA=HAVE OUT=WANT;
BY ID CARD;
VAR SCORE;
RUN;

The idea is this but much larger. One ID,CARD has up to 40 Scores, so I was expecting
about score1--score40, but I get significantly more and I can't replicate it here.

id card score1--score40
123 1111
123 1113
456 2221
456 2222

Re: When I proc transpose my data by group1 group2, why I get many blanks values?

sbopster — Sun, 23 May 2021 01:10:02 GMT

basically I need to transpose it so I can search through score1-score40 and

1.check if the first letter has changed within id,card and flag if it has

2. check if the first is same, but second changed within id, card and flag.

this data doesn't make sense, but the real data and variables make sense to what is desired.

I have transposed data many times, but something I can't pinpoint is causing extra variables with blanks and score variable has no blanks.

Re: When I proc transpose my data by group1 group2, why I get many blanks values?

Tom — Sun, 23 May 2021 01:43:21 GMT

@sbopster wrote:

basically I need to transpose it so I can search through score1-score40 and

1.check if the first letter has changed within id,card and flag if it has

2. check if the first is same, but second changed within id, card and flag.

this data doesn't make sense, but the real data and variables make sense to what is desired.

I have transposed data many times, but something I can't pinpoint is causing extra variables with blanks and score variable has no blanks.

That test is going to be much harder after the transpose.

It is not at all clear what you want to do but let's assume that first you need to test if the first letter has changed.

data have;
  input ID Card SCORE $;
cards;
123 1111 AE
123 1111 AB
123 1113 AE
456 2221 AS
456 2221 BF
456 2221 BF
456 2222 DC
456 2222 BE
456 2222 DL
456 2222 BF
456 2222 DC
456 2222 BE
456 2222 DE
;

data want;
  set have;
  by id card;
  change = char(score,1) ne: lag(score);
  if first.card then change=0 ;
run;

Results:

Obs     ID    Card    SCORE    change

  1    123    1111     AE         0
  2    123    1111     AB         0
  3    123    1113     AE         0
  4    456    2221     AS         0
  5    456    2221     BF         1
  6    456    2221     BF         0
  7    456    2222     DC         0
  8    456    2222     BE         1
  9    456    2222     DL         1
 10    456    2222     BF         1
 11    456    2222     DC         1
 12    456    2222     BE         1
 13    456    2222     DE         1

Re: When I proc transpose my data by group1 group2, why I get many blanks values?

Tom — Sun, 23 May 2021 01:54:00 GMT

If you are getting more observations per group than you expected then your grouping variables are not unique.

It might be there are a lot of observations with missing values or one or both of your key variables.

It might be your variables are too short. Or you tried to stuff a 20 digit string into a number instead of character variable.