tag:blogger.com,1999:blog-7958828565254404797.post4880577399060790416..comments2024-03-29T00:02:14.892-07:00Comments on ListenData: How to Transpose Multiple Variables in SASDeepanshu Bhallahttp://www.blogger.com/profile/09802839558125192674noreply@blogger.comBlogger17125tag:blogger.com,1999:blog-7958828565254404797.post-23601558223381436102021-05-08T13:08:46.575-07:002021-05-08T13:08:46.575-07:00This is great and very helpful. thanks Deepanshu :...This is great and very helpful. thanks Deepanshu :)Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-58408482773355521442021-02-25T13:43:27.332-08:002021-02-25T13:43:27.332-08:00SAS Error - Variable name truncation; 32-character...SAS Error - Variable name truncation; 32-character restriction<br /><br />Hello, I am currently using SAS Studio through On Demand for Academics to complete a project.<br /><br />The current technical issue arises when creating a SAS table whose column headers are given the names of distinct values of a single column from another SAS table.<br /><br /> This is the basic SAS code I am using to turn ALLTAGS into a table with the values as column headers:<br /><br /><br />__________________________________________________<br />/* turn distinct tags into column headers*/<br /><br />proc sql;<br /> create table alltags<br /> as<br /> select distinct<br /> tag, count(distinct tag) as count<br /> from phase2.q1_10q;<br />quit;<br /><br /><br />proc transpose<br /> data = work.alltags<br /> out=tagsAsFields<br /> ;<br /> ID tag;<br /> var count;<br /><br /><br />I receive the error:<br /><br />ERROR: The ID value "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" occurs twice in the input data set.<br /><br />Where “"XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" contains the first 32 characters of the first instance encountered where the first 32 characters of the string are not distinct from the first 32 characters of a previously generated column.<br /><br />e.g.<br /><br />Both<br /> <br /> “APICShareBasedPaymentArrangementAcquisitionsIncreaseForCostRecognition”<br /><br />And<br /><br /> “APICShareBasedPaymentArrangementIncreaseDecreaseForCostRecognition”<br /><br />Become<br /><br /> “APICShareBasedPaymentArrangement”<br />”<br />So they cannot both be made column names.<br />____________________________________________<br /><br />I understand that I can create a table with headers >32 characters outside of SAS, then when I import the table to SAS specify variable names (32 char or less) in the data step.<br /><br />However, the data set I am transposing contains >8000 distinct entries, ~5000 of which are >32 characters. Manually renaming each field in the data step is not a feasible task (unless properly automated in some way).<br /><br />The final data set is to be imported into SAS Enterprise Miner 15.1 Where analysis will take place. It is my current understanding that the 32-character limit will be imposed again when the data set is imported into Enterprise Miner. Can this restriction be bypassed?<br /><br />Our research also suggested that variable labels can be used to store strings >32 character and remain permanently associated with the variable/column/field in the metadata. Unless there is a method of automating such label assignment, the size of the dataset prohibits this approach. Some support.sas threads suggest these labels may be deprecated in some way.<br /><br />If there is a known solution to bypassing the 32-character limit in column names for SAS tables(in Studio and/or EM), please provide details that would assist me in completing this task. Thank you for your time.Jay Annadathahttps://www.blogger.com/profile/09446957563168891389noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-29755278272783162712020-10-30T12:09:34.745-07:002020-10-30T12:09:34.745-07:00Thank you so much. This post is so valuable. I tri...Thank you so much. This post is so valuable. I tried several ways but were too much coding. I tried yours with minor modify and it works perfectly. In the 1st proc transpose, I used all the variable that I need to reshape and in the 2nd proc transpose, I use "by id time" rather than "by id" only as you shown above. <br />So appreciate your sharing.<br />DThttps://www.blogger.com/profile/08560082160717883779noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-71388080168135999192020-07-21T06:49:29.264-07:002020-07-21T06:49:29.264-07:00Respect+Respect+Anonymoushttps://www.blogger.com/profile/12664079669219732959noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-6146819858574951042020-03-11T03:16:43.283-07:002020-03-11T03:16:43.283-07:00Use
Option missing=0;
It will replace missing val...Use <br />Option missing=0;<br />It will replace missing value as 0, you can also pass value as per ur requirement<br />Laukesh https://www.blogger.com/profile/15827499716546195785noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-6684457165745549492020-03-11T02:57:19.327-07:002020-03-11T02:57:19.327-07:00/*we can not use groupconcat function over proc SQ.../*we can not use groupconcat function over proc SQL so we use retain statement here*/<br /><br />DATA XYZ(KEEP=ID KEEP=GENDER KEEP=NEW_COL);<br />SET A;<br />BY ID GENDER;<br />RETAIN NEW_COL;<br />LENGTH NEW_COL $500.;<br />IF FIRST.ID THEN NEW_COL=SALARY;<br />ELSE NEW_COL=CATX(',',NEW_COL,SALARY);<br />IF LAST.ID THEN OUTPUT ;<br />RUN;Laukesh https://www.blogger.com/profile/15827499716546195785noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-56313807956849373782020-03-11T02:53:14.100-07:002020-03-11T02:53:14.100-07:00This comment has been removed by the author.Laukesh https://www.blogger.com/profile/15827499716546195785noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-8466950722561909802019-06-11T00:12:03.879-07:002019-06-11T00:12:03.879-07:00Thank you very much for the answer.
I have read i...Thank you very much for the answer. <br />I have read in several sites that the proc transpose is a very slow procedure for large data files that is my case. There is some other option in SAS code to do it. <br /><br />Regards,<br />natàlianataliahttps://www.blogger.com/profile/18217598636754911391noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-393669672973330932019-06-10T14:02:39.256-07:002019-06-10T14:02:39.256-07:00In case you want zeros instead of missing values i...In case you want zeros instead of missing values in PROC TRANSPOSE output, you can add the following program after running proc transpose.<br /><br />data out1;<br /> set out1;<br /> array replace _numeric_;<br /> do over replace;<br /> if replace=. then replace=0;<br /> end;<br />run;Deepanshu Bhallahttps://www.blogger.com/profile/09802839558125192674noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-82770949079750422152019-06-10T13:54:55.414-07:002019-06-10T13:54:55.414-07:00data mydata;
input CONTRACT IND MONTH1 MONTH2 MONT...data mydata;<br />input CONTRACT IND MONTH1 MONTH2 MONTH3;<br />cards;<br />1 100 10 20 30<br />1 200 30 10 10<br />2 100 20 20 20<br />2 300 10 20 30<br />;<br />run;<br /><br />proc transpose data=mydata prefix=IND out=out1(rename=(_name_=Month));;<br />by CONTRACT;<br />var MONTH1-MONTH3;<br />id IND;<br />run;Deepanshu Bhallahttps://www.blogger.com/profile/09802839558125192674noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-22237506596050888562019-06-10T13:39:38.089-07:002019-06-10T13:39:38.089-07:00Hello,
I have this dataset:
CONTRACT IND MONTH1 M...Hello,<br />I have this dataset:<br /><br />CONTRACT IND MONTH1 MONTH2 MONTH3<br />1 100 10 20 30<br />1 200 30 10 10<br />2 100 20 20 20<br />2 300 10 20 30<br /><br />I need this dataset:<br /><br />CONTRACT MONTH IND100 IND200 IND300<br />1 month1 10 30 0<br />1 month2 20 10 0<br />1 month3 30 10 0<br />2 month1 20 0 10<br />2 month2 20 0 20<br />2 month3 20 0 30<br /><br />Can you help me?<br />Thank younataliahttps://www.blogger.com/profile/18217598636754911391noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-37611656183607708002019-06-10T13:34:51.720-07:002019-06-10T13:34:51.720-07:00This comment has been removed by the author.nataliahttps://www.blogger.com/profile/18217598636754911391noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-76897467706943848542019-06-09T09:06:26.361-07:002019-06-09T09:06:26.361-07:00First sort d data by id then
Proc transpose data=e...First sort d data by id then<br />Proc transpose data=example out=example1(drop=_name_) prefix=expenses;<br />Var expenses;<br />By acct_id gender;<br />Run;<br />Then use catx <br />Data ex2(drop=expenses expenses2 expenses 3);<br />Set example1;<br />Expenses=catx(',',expenses1,expenses,expenses);<br />Run;<br />Kiranhttps://www.blogger.com/profile/10199816324573583395noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-55431650396783255302019-03-08T00:14:16.513-08:002019-03-08T00:14:16.513-08:00I Have transaction dataset in which I have a colum...I Have transaction dataset in which I have a column of expenses I want to keep all transactions side by side using comma based on the account id wise.Below i have mentioned small scenario of the one.<br /><br />Acct_Id gender expenses<br />101 M 20000<br />102 F 20000<br />103 F 50000<br />101 M 10000<br />103 F 18000<br />102 F 21000<br />102 F 11000<br />103 F 49000<br />101 M 20000<br />I want all expenses in one column side by side using deimeter as comaa, I want it as below in SAS, Can anyone please assist me in doing this will be a great help for me.<br /><br />101 M 20000,10000,20000<br />102 F 20000,21000,11000<br />103 F 50000,18000,49000<br />Thanks and regards, <br />SwarupaAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-16114378232974976642018-04-24T12:27:37.716-07:002018-04-24T12:27:37.716-07:00Hi, when I run your last example code above, I got...Hi, when I run your last example code above, I got error messages:<br />proc transpose data=out1 delimiter=_ out=new2(drop=_name_);<br /> ---------<br /> 22<br /> 76<br />ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATA, LABEL, LET, NAME, OUT,<br /> PREFIX.<br />ERROR 76-322: Syntax error, statement will be ignored.<br />328 by ID;<br />329 var col1;<br />330 id _name_ time;<br /> -----<br /> 22 200<br />ERROR 22-322: Expecting ;.<br />ERROR 200-322: The symbol is not recognized and will be ignored.<br />331 run;<br /><br />NOTE: The SAS System stopped processing this step because of errors.<br />NOTE: PROCEDURE TRANSPOSE used (Total process time):<br /> real time 0.05 seconds<br /> cpu time 0.04 seconds<br /><br />Why is that? Thank you very much for your help!wyalmwhttps://www.blogger.com/profile/09414355674496672825noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-24507391730649093522017-11-20T10:20:12.790-08:002017-11-20T10:20:12.790-08:00plz provide more examples.....Tx..plz provide more examples.....Tx..Anonymoushttps://www.blogger.com/profile/09814959228839186930noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-63111155894842496882017-01-21T03:35:33.983-08:002017-01-21T03:35:33.983-08:00thanks for your information
thanks for your information<br />Anonymousnoreply@blogger.com