BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dcortell
Pyrite | Level 9

I have the following two codes:

proc transpose data=rank_score out=rank_score_tr
(drop=_name_ _suffix_);
by url;
id _term_;
var N;
run;

option VALIDVARNAME=ANY;

proc transpose data=rank_score out=rank_score_tr2
(drop=_name_ _suffix_) prefix=rank_ ;
by url;
id _term_;
var rankcount;
run;

The dataset include in the _term_ column combinations of maximum two letters, with the chance of having both "a" and " a" or "a ", including spaces.

 

The first transpose works like a charm.

The second, adding prefix, seems compressing the blanks and provide error of duplicate at BY level

 

I checked the documentation but no track of having this kind of compression using the prefix statement.

 

Anyone knowing why it is happening and how to stop the space compression?

1 ACCEPTED SOLUTION

Accepted Solutions
dcortell
Pyrite | Level 9

Actually I could solve adding the prefix generating a new variable and then transposing it as ID, but I was curios to understand why the transpose via prefix was generating this kind of behavior

View solution in original post

7 REPLIES 7
msauer
Obsidian | Level 7
Using suffix instead of prefix seems to work.

Although you produce two columns basically indistinguishable. Wouldn't it be better to replace the blank in the first place, i. e. by an underscore? But you probably have your reasons.
dcortell
Pyrite | Level 9

Can't do that as we can also have "_a" or " a" and both of them must count as separate variables

msauer
Obsidian | Level 7
What about enclosing the _term_ column by an underscore before doing the transpose ...?
dcortell
Pyrite | Level 9

Actually I could solve adding the prefix generating a new variable and then transposing it as ID, but I was curios to understand why the transpose via prefix was generating this kind of behavior

Oligolas
Barite | Level 11

Your second statement says you want to create one row per url and use the content of _term_ as new variables (named rank_A_A and rank_A) and the value of these variables has to be rankcount.

 

So,

first, since "rank_A A" is not a valid variable name, SAS inserts an underscores to match the restrictions and creates 'rank_A_A'

second, if for a same url you have more than one time the same _term_ SAS will not know which of the rankcount values it has to put as new value of the rank_ variables. That's why you get the errors in your log. Your combination of by group and id group needs to be unique.

 

Consider posting a simple example of the data you have and what you'd expect

 

________________________

- Cheers -

dcortell
Pyrite | Level 9
As stated in the description proc transpose accept blanks in the variable name, it is only when prefix it is used that the blanks are removed, and this cause the proc to detect by and id groups duplication, where there are none. As example I have provided the above where for the same url (by) I have to value in the ID group " a" and "a", first including blank, second without blank. Underscore can't be used as there could be also values like "_a" in the id group. In the end the only solution I found to keep the blanks in the variable name by adding also a prefix is to generate a new variable column with the desired prefix BEFORE the proc transpose.
Oligolas
Barite | Level 11

oh yeah I see, you use VALIDVARNAME=ANY and get the results you want.

I acknowledge your trick works. 

As far as I know, this means you have to work with SAS named literals if you further want to proceed the variables.

I'd get chills just thinking about it but I haven't the big picture of what you're trying to do and it's your code in the end.

data test1;
length url $30 _term_ $10;
url='kjahskdhfkh';
_term_=' A A';
rankcount=1;
output;
url='kjahskdhfkh';
_term_='A A';
rankcount=2;
output;
url='kjahskdhfkh';
_term_='A';
rankcount=3;
output;
url='kjahskdhfkh';
_term_=' A';
rankcount=1;
output;
url='kjahskdhfkh';
_term_='_A';
rankcount=1;
output;
run;

data test1;
   set test1;
   _term_=cat('_',_term_);
run;

option VALIDVARNAME=ANY;

proc transpose data=test1 out=test2
(drop=_name_ _suffix_) prefix=rank_ ;
by url;
id _term_;
var rankcount;
run;

data test3;
   set test2;
   'rank__ A A'n='rank__ A A'n+100;
run;

 

 

 

________________________

- Cheers -

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 2605 views
  • 2 likes
  • 3 in conversation