Solved: Re: Convert an excel data table into SAS format

saraphdnz · Posted 06-26-2020 10:54 PM

Hello there,

I have an excel data set in the following format (sample data attached) for 5000 firms with 324 months of data.

I need the following structure of rows and columns:

ID, Date, Y, X.

it is assumed that the data is sorted or can be sorted by ID and DATE, such that within each ID block, the observations are in DATE order.

I have created a dataset in excel manually (attached as DSET1) to match with this structure and able to import and run the macro on the sample but I am unable to find anything that can convert this big data structure as required in SAS.

Do I need to look in Excel or I can do it in SAS as well? I would appreciate it if I can get some guidance on this.

Regards,

Sara

Kurt_Bremser · Posted 06-27-2020 05:39 AM

This should give you a start:

proc import
  datafile='/folders/myfolders/sample data file.xlsx'
  dbms=xlsx
  out=sample (
    drop=l m n o p q r s t u __ ___1
    rename=(id_date=id)
    where=(id ne ' .')
  )
  replace
;
sheet="Sheet2";
run;

proc sort data=sample;
by id;
run;

proc transpose
  data=sample
  out=long (
    drop=_name_
    rename=(col1=y)
    where=(y ne ' -')
  )
;
by id;
var _:;
run;

data want;
set long;
date = input(_label_,5.) + '30dec1899'd;
format date yymmd7.;
drop _label_;
run;

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

View solution in original post

Kurt_Bremser · Posted 06-27-2020 05:39 AM

This should give you a start:

proc import
  datafile='/folders/myfolders/sample data file.xlsx'
  dbms=xlsx
  out=sample (
    drop=l m n o p q r s t u __ ___1
    rename=(id_date=id)
    where=(id ne ' .')
  )
  replace
;
sheet="Sheet2";
run;

proc sort data=sample;
by id;
run;

proc transpose
  data=sample
  out=long (
    drop=_name_
    rename=(col1=y)
    where=(y ne ' -')
  )
;
by id;
var _:;
run;

data want;
set long;
date = input(_label_,5.) + '30dec1899'd;
format date yymmd7.;
drop _label_;
run;

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

saraphdnz · Posted 06-28-2020 04:41 AM

Thanks, @Kurt_Bremser for the code.

I ran the first part of the program but it changes my date format which was in the first row as numeric values in my sample sheet and dates still appearing in the first row and not as a column which is the requirement of the structure I need.

Please advise.

proc import
  datafile='H:\sample data file.xlsx'
  dbms=xlsx
  out=sample (
    drop=MX MY MZ NA NB NC __ ___1
    rename=(id_date=id)
    where=(id ne '.')
  )
  replace
;
sheet="Sheet1";
run;

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         proc import
 74           datafile='H:\Paper 3 SAS - Single Share Class\SAS MAcro help docs\sample data file.xlsx'
 75           dbms=xlsx
 76           out=sample (
 77             drop=MX MY MZ NA NB NC __ ___1
 78             rename=(id_date=id)
 79             where=(id ne '.')
 80           )
 81           replace
 82         ;
 83         sheet="Sheet1";
 84         run;
 
 NOTE: One or more variables were converted because the data type is not supported by the V9 engine. 
       For more details, run with options MSGLEVEL=I.
 WARNING: The variable MX in the DROP, KEEP, or RENAME list has never been referenced.
 WARNING: The variable MY in the DROP, KEEP, or RENAME list has never been referenced.
 WARNING: The variable MZ in the DROP, KEEP, or RENAME list has never been referenced.
 WARNING: The variable NA in the DROP, KEEP, or RENAME list has never been referenced.
 WARNING: The variable NB in the DROP, KEEP, or RENAME list has never been referenced.
 WARNING: The variable NC in the DROP, KEEP, or RENAME list has never been referenced.
 WARNING: The variable __ in the DROP, KEEP, or RENAME list has never been referenced.
 WARNING: The variable ___1 in the DROP, KEEP, or RENAME list has never been referenced.
 NOTE: The import data set has 25 observations and 361 variables.
 NOTE: WORK.SAMPLE data set was successfully created.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.08 seconds
       cpu time            0.09 seconds
       

 
 85         
 86         
 87         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 100

Regards,

Sara

Kurt_Bremser · Posted 06-28-2020 05:39 AM

You may have noticed that there are additional steps in my code. Why don't you use them, as they are essential for the wanted result?

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

Kurt_Bremser · Posted 06-28-2020 05:45 AM

PS the code I gave you works with the file you posted, and with

options validvarname=v7;

You need to adapt it to your real data, which can only be done by inspecting the raw result of the import before you add any drop= dataset options.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

saraphdnz · Posted 06-28-2020 09:13 PM

Thanks @Kurt_Bremser for your comments. I am able to run the program but getting where clause error. I checked the contents of my data to see the format and all the variables are numeric (see below image) but not sure why I am getting this where clause error. Please can you advise what should I need to change here?

options validvarname=v7;
proc import
  datafile='H:\Paper 3 SAS - Single Share Class\SAS MAcro help docs\sample data file.xlsx'
  dbms=xlsx
  out=sample (
     drop=MX MY MZ NA NB NC __ ___1
    rename=(id_date=id)
    where=(id ne '.')
  )
  replace
;
sheet="Sheet1";
run;

proc sort data=sample;
by id;
run;

Proc contents data=work.sample;
run;

proc transpose
  data=sample
  out=long (
    drop=_name_
    rename=(col1=y)
    where=(y ne ' .')
  )
;
by id;
var _:;
run;

data want;
set long;
date = input(_label_,5.) + '30dec1899'd;
format date yymmd7.;
drop _label_;
run;

Log



 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         options validvarname=v7;
 74         proc import
 75           datafile='H:\Paper 3 SAS - Single Share Class\SAS MAcro help docs\sample data file.xlsx'
 76           dbms=xlsx
 77           out=sample (
 78              drop=MX MY MZ NA NB NC __ ___1
 79             rename=(id_date=id)
 80             where=(id ne '.')
 81           )
 82           replace
 83         ;
 84         sheet="Sheet1";
 85         run;
 
 NOTE:    Variable Name Change.  32874 -> _32874                          
 NOTE:    Variable Name Change.  32905 -> _32905                          
 NOTE:    Variable Name Change.  32933 -> _32933                          
 NOTE:    Variable Name Change.  32964 -> _32964                          
 NOTE:    Variable Name Change.  32994 -> _32994                          
 NOTE:    Variable Name Change.  33025 -> _33025                          
 NOTE:    Variable Name Change.  33055 -> _33055                          
 NOTE:    Variable Name Change.  33086 -> _33086                          
 NOTE:    Variable Name Change.  33117 -> _33117                          
 NOTE:    Variable Name Change.  33147 -> _33147                          
 NOTE:    Variable Name Change.  33178 -> _33178                          
 NOTE:    Variable Name Change.  33208 -> _33208                          
 NOTE:    Variable Name Change.  33239 -> _33239                          
 NOTE:    Variable Name Change.  33270 -> _33270                          
 NOTE:    Variable Name Change.  33298 -> _33298                          
 NOTE:    Variable Name Change.  33329 -> _33329                          
 NOTE:    Variable Name Change.  33359 -> _33359                          
 NOTE:    Variable Name Change.  33390 -> _33390                          
 NOTE:    Variable Name Change.  33420 -> _33420                          
 NOTE:    Variable Name Change.  33451 -> _33451                          
 NOTE:    Variable Name Change.  33482 -> _33482                          
 NOTE:    Variable Name Change.  33512 -> _33512                          
 NOTE:    Variable Name Change.  33543 -> _33543                          
 NOTE:    Variable Name Change.  33573 -> _33573                          
 NOTE:    Variable Name Change.  33604 -> _33604                          
 NOTE:    Variable Name Change.  33635 -> _33635                          
 NOTE:    Variable Name Change.  33664 -> _33664                          
 NOTE:    Variable Name Change.  33695 -> _33695                          
 NOTE:    Variable Name Change.  33725 -> _33725                          
 NOTE:    Variable Name Change.  33756 -> _33756                          
 NOTE:    Variable Name Change.  33786 -> _33786                          
 NOTE:    Variable Name Change.  33817 -> _33817                          
 NOTE:    Variable Name Change.  33848 -> _33848                          
 NOTE:    Variable Name Change.  33878 -> _33878                          
 NOTE:    Variable Name Change.  33909 -> _33909                          
 NOTE:    Variable Name Change.  33939 -> _33939                          
 NOTE:    Variable Name Change.  33970 -> _33970                          
 NOTE:    Variable Name Change.  34001 -> _34001                          
 NOTE:    Variable Name Change.  34029 -> _34029                          
 NOTE:    Variable Name Change.  34060 -> _34060                          
 NOTE:    Variable Name Change.  34090 -> _34090                          
 NOTE:    Variable Name Change.  34121 -> _34121                          
 NOTE:    Variable Name Change.  34151 -> _34151                          
 NOTE:    Variable Name Change.  34182 -> _34182                          
 NOTE:    Variable Name Change.  34213 -> _34213                          
 NOTE:    Variable Name Change.  34243 -> _34243                          
 NOTE:    Variable Name Change.  34274 -> _34274                          
 NOTE:    Variable Name Change.  34304 -> _34304                          
 NOTE:    Variable Name Change.  34335 -> _34335                          
 NOTE:    Variable Name Change.  34366 -> _34366                          
 NOTE:    Variable Name Change.  34394 -> _34394                          
 NOTE:    Variable Name Change.  34425 -> _34425                          
 NOTE:    Variable Name Change.  34455 -> _34455                          
 NOTE:    Variable Name Change.  34486 -> _34486                          
 NOTE:    Variable Name Change.  34516 -> _34516                          
 NOTE:    Variable Name Change.  34547 -> _34547                          
 NOTE:    Variable Name Change.  34578 -> _34578                          
 NOTE:    Variable Name Change.  34608 -> _34608                          
 NOTE:    Variable Name Change.  34639 -> _34639                          
 NOTE:    Variable Name Change.  34669 -> _34669                          
 NOTE:    Variable Name Change.  34700 -> _34700                          
 NOTE:    Variable Name Change.  34731 -> _34731                          
 NOTE:    Variable Name Change.  34759 -> _34759                          
 NOTE:    Variable Name Change.  34790 -> _34790                          
 NOTE:    Variable Name Change.  34820 -> _34820                          
 NOTE:    Variable Name Change.  34851 -> _34851                          
 NOTE:    Variable Name Change.  34881 -> _34881                          
 NOTE:    Variable Name Change.  34912 -> _34912                          
 NOTE:    Variable Name Change.  34943 -> _34943                          
 NOTE:    Variable Name Change.  34973 -> _34973                          
 NOTE:    Variable Name Change.  35004 -> _35004                          
 NOTE:    Variable Name Change.  35034 -> _35034                          
 NOTE:    Variable Name Change.  35065 -> _35065                          
 NOTE:    Variable Name Change.  35096 -> _35096                          
 NOTE:    Variable Name Change.  35125 -> _35125                          
 NOTE:    Variable Name Change.  35156 -> _35156                          
 NOTE:    Variable Name Change.  35186 -> _35186                          
 NOTE:    Variable Name Change.  35217 -> _35217                          
 NOTE:    Variable Name Change.  35247 -> _35247                          
 NOTE:    Variable Name Change.  35278 -> _35278                          
 NOTE:    Variable Name Change.  35309 -> _35309                          
 NOTE:    Variable Name Change.  35339 -> _35339                          
 NOTE:    Variable Name Change.  35370 -> _35370                          
 NOTE:    Variable Name Change.  35400 -> _35400                          
 NOTE:    Variable Name Change.  35431 -> _35431                          
 NOTE:    Variable Name Change.  35462 -> _35462                          
 NOTE:    Variable Name Change.  35490 -> _35490                          
 NOTE:    Variable Name Change.  35521 -> _35521                          
 NOTE:    Variable Name Change.  35551 -> _35551                          
 NOTE:    Variable Name Change.  35582 -> _35582                          
 NOTE:    Variable Name Change.  35612 -> _35612                          
 NOTE:    Variable Name Change.  35643 -> _35643                          
 NOTE:    Variable Name Change.  35674 -> _35674                          
 NOTE:    Variable Name Change.  35704 -> _35704                          
 NOTE:    Variable Name Change.  35735 -> _35735                          
 NOTE:    Variable Name Change.  35765 -> _35765                          
 NOTE:    Variable Name Change.  35796 -> _35796                          
 NOTE:    Variable Name Change.  35827 -> _35827                          
 NOTE:    Variable Name Change.  35855 -> _35855                          
 NOTE:    Variable Name Change.  35886 -> _35886                          
 NOTE:    Variable Name Change.  35916 -> _35916                          
 NOTE:    Variable Name Change.  35947 -> _35947                          
 NOTE:    Variable Name Change.  35977 -> _35977                          
 NOTE:    Variable Name Change.  36008 -> _36008                          
 NOTE:    Variable Name Change.  36039 -> _36039                          
 NOTE:    Variable Name Change.  36069 -> _36069                          
 NOTE:    Variable Name Change.  36100 -> _36100                          
 NOTE:    Variable Name Change.  36130 -> _36130                          
 NOTE:    Variable Name Change.  36161 -> _36161                          
 NOTE:    Variable Name Change.  36192 -> _36192                          
 NOTE:    Variable Name Change.  36220 -> _36220                          
 NOTE:    Variable Name Change.  36251 -> _36251                          
 NOTE:    Variable Name Change.  36281 -> _36281                          
 NOTE:    Variable Name Change.  36312 -> _36312                          
 NOTE:    Variable Name Change.  36342 -> _36342                          
 NOTE:    Variable Name Change.  36373 -> _36373                          
 NOTE:    Variable Name Change.  36404 -> _36404                          
 NOTE:    Variable Name Change.  36434 -> _36434                          
 NOTE:    Variable Name Change.  36465 -> _36465                          
 NOTE:    Variable Name Change.  36495 -> _36495                          
 NOTE:    Variable Name Change.  36526 -> _36526                          
 NOTE:    Variable Name Change.  36557 -> _36557                          
 NOTE:    Variable Name Change.  36586 -> _36586                          
 NOTE:    Variable Name Change.  36617 -> _36617                          
 NOTE:    Variable Name Change.  36647 -> _36647                          
 NOTE:    Variable Name Change.  36678 -> _36678                          
 NOTE:    Variable Name Change.  36708 -> _36708                          
 NOTE:    Variable Name Change.  36739 -> _36739                          
 NOTE:    Variable Name Change.  36770 -> _36770                          
 NOTE:    Variable Name Change.  36800 -> _36800                          
 NOTE:    Variable Name Change.  36831 -> _36831                          
 NOTE:    Variable Name Change.  36861 -> _36861                          
 NOTE:    Variable Name Change.  36892 -> _36892                          
 NOTE:    Variable Name Change.  36923 -> _36923                          
 NOTE:    Variable Name Change.  36951 -> _36951                          
 NOTE:    Variable Name Change.  36982 -> _36982                          
 NOTE:    Variable Name Change.  37012 -> _37012                          
 NOTE:    Variable Name Change.  37043 -> _37043                          
 NOTE:    Variable Name Change.  37073 -> _37073                          
 NOTE:    Variable Name Change.  37104 -> _37104                          
 NOTE:    Variable Name Change.  37135 -> _37135                          
 NOTE:    Variable Name Change.  37165 -> _37165                          
 NOTE:    Variable Name Change.  37196 -> _37196                          
 NOTE:    Variable Name Change.  37226 -> _37226                          
 NOTE:    Variable Name Change.  37257 -> _37257                          
 NOTE:    Variable Name Change.  37288 -> _37288                          
 NOTE:    Variable Name Change.  37316 -> _37316                          
 NOTE:    Variable Name Change.  37347 -> _37347                          
 NOTE:    Variable Name Change.  37377 -> _37377                          
 NOTE:    Variable Name Change.  37408 -> _37408                          
 NOTE:    Variable Name Change.  37438 -> _37438                          
 NOTE:    Variable Name Change.  37469 -> _37469                          
 NOTE:    Variable Name Change.  37500 -> _37500                          
 NOTE:    Variable Name Change.  37530 -> _37530                          
 NOTE:    Variable Name Change.  37561 -> _37561                          
 NOTE:    Variable Name Change.  37591 -> _37591                          
 NOTE:    Variable Name Change.  37622 -> _37622                          
 NOTE:    Variable Name Change.  37653 -> _37653                          
 NOTE:    Variable Name Change.  37681 -> _37681                          
 NOTE:    Variable Name Change.  37712 -> _37712                          
 NOTE:    Variable Name Change.  37742 -> _37742                          
 NOTE:    Variable Name Change.  37773 -> _37773                          
 NOTE:    Variable Name Change.  37803 -> _37803                          
 NOTE:    Variable Name Change.  37834 -> _37834                          
 NOTE:    Variable Name Change.  37865 -> _37865                          
 NOTE:    Variable Name Change.  37895 -> _37895                          
 NOTE:    Variable Name Change.  37926 -> _37926                          
 NOTE:    Variable Name Change.  37956 -> _37956                          
 NOTE:    Variable Name Change.  37987 -> _37987                          
 NOTE:    Variable Name Change.  38018 -> _38018                          
 NOTE:    Variable Name Change.  38047 -> _38047                          
 NOTE:    Variable Name Change.  38078 -> _38078                          
 NOTE:    Variable Name Change.  38108 -> _38108                          
 NOTE:    Variable Name Change.  38139 -> _38139                          
 NOTE:    Variable Name Change.  38169 -> _38169                          
 NOTE:    Variable Name Change.  38200 -> _38200                          
 NOTE:    Variable Name Change.  38231 -> _38231                          
 NOTE:    Variable Name Change.  38261 -> _38261                          
 NOTE:    Variable Name Change.  38292 -> _38292                          
 NOTE:    Variable Name Change.  38322 -> _38322                          
 NOTE:    Variable Name Change.  38353 -> _38353                          
 NOTE:    Variable Name Change.  38384 -> _38384                          
 NOTE:    Variable Name Change.  38412 -> _38412                          
 NOTE:    Variable Name Change.  38443 -> _38443                          
 NOTE:    Variable Name Change.  38473 -> _38473                          
 NOTE:    Variable Name Change.  38504 -> _38504                          
 NOTE:    Variable Name Change.  38534 -> _38534                          
 NOTE:    Variable Name Change.  38565 -> _38565                          
 NOTE:    Variable Name Change.  38596 -> _38596                          
 NOTE:    Variable Name Change.  38626 -> _38626                          
 NOTE:    Variable Name Change.  38657 -> _38657                          
 NOTE:    Variable Name Change.  38687 -> _38687                          
 NOTE:    Variable Name Change.  38718 -> _38718                          
 NOTE:    Variable Name Change.  38749 -> _38749                          
 NOTE:    Variable Name Change.  38777 -> _38777                          
 NOTE:    Variable Name Change.  38808 -> _38808                          
 NOTE:    Variable Name Change.  38838 -> _38838                          
 NOTE:    Variable Name Change.  38869 -> _38869                          
 NOTE:    Variable Name Change.  38899 -> _38899                          
 NOTE:    Variable Name Change.  38930 -> _38930                          
 NOTE:    Variable Name Change.  38961 -> _38961                          
 NOTE:    Variable Name Change.  38991 -> _38991                          
 NOTE:    Variable Name Change.  39022 -> _39022                          
 NOTE:    Variable Name Change.  39052 -> _39052                          
 NOTE:    Variable Name Change.  39083 -> _39083                          
 NOTE:    Variable Name Change.  39114 -> _39114                          
 NOTE:    Variable Name Change.  39142 -> _39142                          
 NOTE:    Variable Name Change.  39173 -> _39173                          
 NOTE:    Variable Name Change.  39203 -> _39203                          
 NOTE:    Variable Name Change.  39234 -> _39234                          
 NOTE:    Variable Name Change.  39264 -> _39264                          
 NOTE:    Variable Name Change.  39295 -> _39295                          
 NOTE:    Variable Name Change.  39326 -> _39326                          
 NOTE:    Variable Name Change.  39356 -> _39356                          
 NOTE:    Variable Name Change.  39387 -> _39387                          
 NOTE:    Variable Name Change.  39417 -> _39417                          
 NOTE:    Variable Name Change.  39448 -> _39448                          
 NOTE:    Variable Name Change.  39479 -> _39479                          
 NOTE:    Variable Name Change.  39508 -> _39508                          
 NOTE:    Variable Name Change.  39539 -> _39539                          
 NOTE:    Variable Name Change.  39569 -> _39569                          
 NOTE:    Variable Name Change.  39600 -> _39600                          
 NOTE:    Variable Name Change.  39630 -> _39630                          
 NOTE:    Variable Name Change.  39661 -> _39661                          
 NOTE:    Variable Name Change.  39692 -> _39692                          
 NOTE:    Variable Name Change.  39722 -> _39722                          
 NOTE:    Variable Name Change.  39753 -> _39753                          
 NOTE:    Variable Name Change.  39783 -> _39783                          
 NOTE:    Variable Name Change.  39814 -> _39814                          
 NOTE:    Variable Name Change.  39845 -> _39845                          
 NOTE:    Variable Name Change.  39873 -> _39873                          
 NOTE:    Variable Name Change.  39904 -> _39904                          
 NOTE:    Variable Name Change.  39934 -> _39934                          
 NOTE:    Variable Name Change.  39965 -> _39965                          
 NOTE:    Variable Name Change.  39995 -> _39995                          
 NOTE:    Variable Name Change.  40026 -> _40026                          
 NOTE:    Variable Name Change.  40057 -> _40057                          
 NOTE:    Variable Name Change.  40087 -> _40087                          
 NOTE:    Variable Name Change.  40118 -> _40118                          
 NOTE:    Variable Name Change.  40148 -> _40148                          
 NOTE:    Variable Name Change.  40179 -> _40179                          
 NOTE:    Variable Name Change.  40210 -> _40210                          
 NOTE:    Variable Name Change.  40238 -> _40238                          
 NOTE:    Variable Name Change.  40269 -> _40269                          
 NOTE:    Variable Name Change.  40299 -> _40299                          
 NOTE:    Variable Name Change.  40330 -> _40330                          
 NOTE:    Variable Name Change.  40360 -> _40360                          
 NOTE:    Variable Name Change.  40391 -> _40391                          
 NOTE:    Variable Name Change.  40422 -> _40422                          
 NOTE:    Variable Name Change.  40452 -> _40452                          
 NOTE:    Variable Name Change.  40483 -> _40483                          
 NOTE:    Variable Name Change.  40513 -> _40513                          
 NOTE:    Variable Name Change.  40544 -> _40544                          
 NOTE:    Variable Name Change.  40575 -> _40575                          
 NOTE:    Variable Name Change.  40603 -> _40603                          
 NOTE:    Variable Name Change.  40634 -> _40634                          
 NOTE:    Variable Name Change.  40664 -> _40664                          
 NOTE:    Variable Name Change.  40695 -> _40695                          
 NOTE:    Variable Name Change.  40725 -> _40725                          
 NOTE:    Variable Name Change.  40756 -> _40756                          
 NOTE:    Variable Name Change.  40787 -> _40787                          
 NOTE:    Variable Name Change.  40817 -> _40817                          
 NOTE:    Variable Name Change.  40848 -> _40848                          
 NOTE:    Variable Name Change.  40878 -> _40878                          
 NOTE:    Variable Name Change.  40909 -> _40909                          
 NOTE:    Variable Name Change.  40940 -> _40940                          
 NOTE:    Variable Name Change.  40969 -> _40969                          
 NOTE:    Variable Name Change.  41000 -> _41000                          
 NOTE:    Variable Name Change.  41030 -> _41030                          
 NOTE:    Variable Name Change.  41061 -> _41061                          
 NOTE:    Variable Name Change.  41091 -> _41091                          
 NOTE:    Variable Name Change.  41122 -> _41122                          
 NOTE:    Variable Name Change.  41153 -> _41153                          
 NOTE:    Variable Name Change.  41183 -> _41183                          
 NOTE:    Variable Name Change.  41214 -> _41214                          
 NOTE:    Variable Name Change.  41244 -> _41244                          
 NOTE:    Variable Name Change.  41275 -> _41275                          
 NOTE:    Variable Name Change.  41306 -> _41306                          
 NOTE:    Variable Name Change.  41334 -> _41334                          
 NOTE:    Variable Name Change.  41365 -> _41365                          
 NOTE:    Variable Name Change.  41395 -> _41395                          
 NOTE:    Variable Name Change.  41426 -> _41426                          
 NOTE:    Variable Name Change.  41456 -> _41456                          
 NOTE:    Variable Name Change.  41487 -> _41487                          
 NOTE:    Variable Name Change.  41518 -> _41518                          
 NOTE:    Variable Name Change.  41548 -> _41548                          
 NOTE:    Variable Name Change.  41579 -> _41579                          
 NOTE:    Variable Name Change.  41609 -> _41609                          
 NOTE:    Variable Name Change.  41640 -> _41640                          
 NOTE:    Variable Name Change.  41671 -> _41671                          
 NOTE:    Variable Name Change.  41699 -> _41699                          
 NOTE:    Variable Name Change.  41730 -> _41730                          
 NOTE:    Variable Name Change.  41760 -> _41760                          
 NOTE:    Variable Name Change.  41791 -> _41791                          
 NOTE:    Variable Name Change.  41821 -> _41821                          
 NOTE:    Variable Name Change.  41852 -> _41852                          
 NOTE:    Variable Name Change.  41883 -> _41883                          
 NOTE:    Variable Name Change.  41913 -> _41913                          
 NOTE:    Variable Name Change.  41944 -> _41944                          
 NOTE:    Variable Name Change.  41974 -> _41974                          
 NOTE:    Variable Name Change.  42005 -> _42005                          
 NOTE:    Variable Name Change.  42036 -> _42036                          
 NOTE:    Variable Name Change.  42064 -> _42064                          
 NOTE:    Variable Name Change.  42095 -> _42095                          
 NOTE:    Variable Name Change.  42125 -> _42125                          
 NOTE:    Variable Name Change.  42156 -> _42156                          
 NOTE:    Variable Name Change.  42186 -> _42186                          
 NOTE:    Variable Name Change.  42217 -> _42217                          
 NOTE:    Variable Name Change.  42248 -> _42248                          
 NOTE:    Variable Name Change.  42278 -> _42278                          
 NOTE:    Variable Name Change.  42309 -> _42309                          
 NOTE:    Variable Name Change.  42339 -> _42339                          
 NOTE:    Variable Name Change.  42370 -> _42370                          
 NOTE:    Variable Name Change.  42401 -> _42401                          
 NOTE:    Variable Name Change.  42430 -> _42430                          
 NOTE:    Variable Name Change.  42461 -> _42461                          
 NOTE:    Variable Name Change.  42491 -> _42491                          
 NOTE:    Variable Name Change.  42522 -> _42522                          
 NOTE:    Variable Name Change.  42552 -> _42552                          
 NOTE:    Variable Name Change.  42583 -> _42583                          
 NOTE:    Variable Name Change.  42614 -> _42614                          
 NOTE:    Variable Name Change.  42644 -> _42644                          
 NOTE:    Variable Name Change.  42675 -> _42675                          
 NOTE:    Variable Name Change.  42705 -> _42705                          
 NOTE:    Variable Name Change.  42736 -> _42736                          
 NOTE:    Variable Name Change.  42767 -> _42767                          
 NOTE:    Variable Name Change.  42795 -> _42795                          
 NOTE:    Variable Name Change.  42826 -> _42826                          
 NOTE:    Variable Name Change.  42856 -> _42856                          
 NOTE:    Variable Name Change.  42887 -> _42887                          
 NOTE:    Variable Name Change.  42917 -> _42917                          
 NOTE:    Variable Name Change.  42948 -> _42948                          
 NOTE:    Variable Name Change.  42979 -> _42979                          
 NOTE:    Variable Name Change.  43009 -> _43009                          
 NOTE:    Variable Name Change.  43040 -> _43040                          
 NOTE:    Variable Name Change.  43070 -> _43070                          
 NOTE:    Variable Name Change.  43101 -> _43101                          
 NOTE:    Variable Name Change.  43132 -> _43132                          
 NOTE:    Variable Name Change.  43160 -> _43160                          
 NOTE:    Variable Name Change.  43191 -> _43191                          
 NOTE:    Variable Name Change.  43221 -> _43221                          
 NOTE:    Variable Name Change.  43252 -> _43252                          
 NOTE:    Variable Name Change.  43282 -> _43282                          
 NOTE:    Variable Name Change.  43313 -> _43313                          
 NOTE:    Variable Name Change.  43344 -> _43344                          
 NOTE:    Variable Name Change.  43374 -> _43374                          
 NOTE:    Variable Name Change.  43405 -> _43405                          
 NOTE:    Variable Name Change.  43435 -> _43435                          
 NOTE:    Variable Name Change.  43466 -> _43466                          
 NOTE:    Variable Name Change.  43497 -> _43497                          
 NOTE:    Variable Name Change.  43525 -> _43525                          
 NOTE:    Variable Name Change.  43556 -> _43556                          
 NOTE:    Variable Name Change.  43586 -> _43586                          
 NOTE:    Variable Name Change.  43617 -> _43617                          
 NOTE:    Variable Name Change.  43647 -> _43647                          
 NOTE:    Variable Name Change.  43678 -> _43678                          
 NOTE:    Variable Name Change.  43709 -> _43709                          
 NOTE:    Variable Name Change.  43739 -> _43739                          
 NOTE:    Variable Name Change.  43770 -> _43770                          
 NOTE:    Variable Name Change.  43800 -> _43800                          
 NOTE: One or more variables were converted because the data type is not supported by the V9 engine. 
       For more details, run with options MSGLEVEL=I.
 WARNING: The variable MX in the DROP, KEEP, or RENAME list has never been referenced.
 WARNING: The variable MY in the DROP, KEEP, or RENAME list has never been referenced.
 WARNING: The variable MZ in the DROP, KEEP, or RENAME list has never been referenced.
 WARNING: The variable NA in the DROP, KEEP, or RENAME list has never been referenced.
 WARNING: The variable NB in the DROP, KEEP, or RENAME list has never been referenced.
 WARNING: The variable NC in the DROP, KEEP, or RENAME list has never been referenced.
 WARNING: The variable __ in the DROP, KEEP, or RENAME list has never been referenced.
 WARNING: The variable ___1 in the DROP, KEEP, or RENAME list has never been referenced.
 NOTE: The import data set has 25 observations and 361 variables.
 NOTE: WORK.SAMPLE data set was successfully created.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.08 seconds
       cpu time            0.07 seconds
       

 
 86         
 87         proc sort data=sample;
 88         by id;
 89         run;
 
 NOTE: There were 25 observations read from the data set WORK.SAMPLE.
 NOTE: The data set WORK.SAMPLE has 25 observations and 361 variables.
 NOTE: PROCEDURE SORT used (Total process time):
       real time           0.01 seconds
       cpu time            0.01 seconds
       
 
 90         
 91         Proc contents data=work.sample;
 92         run;
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           0.53 seconds
       cpu time            0.50 seconds
       

 
 93         
 94         proc transpose
 95           data=sample
 96           out=long (
 97             drop=_name_
 98             rename=(col1=y)
 99             where=(y ne ' .')
 100          )
 101        ;
 102        by id;
 103        var _:;
 104        run;
 
 ERROR: WHERE clause operator requires compatible variables.
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: There were 25 observations read from the data set WORK.SAMPLE.
 WARNING: The data set WORK.LONG may be incomplete.  When this step was stopped there were 0 
          observations and 3 variables.
 WARNING: Data set WORK.LONG was not replaced because this step was stopped.
 NOTE: PROCEDURE TRANSPOSE used (Total process time):
       real time           0.01 seconds
       cpu time            0.00 seconds
       
 105        
 
 
 106        data want;
 107        set long;
 108        date = input(_label_,5.) + '30dec1899'd;
 109        format date yymmd7.;
 110        drop _label_;
 111        run;
 
 NOTE: There were 9000 observations read from the data set WORK.LONG.
 NOTE: The data set WORK.WANT has 9000 observations and 3 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 112        
 113        OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 126

Regards,

Sara

Kurt_Bremser · Posted 06-29-2020 01:05 AM

Your original sample data had character values, now it seems you have numbers throughout. Adapt the where condition to numeric.

In the import step, you try to drop variables that are not there. The drop was there only because empty columns were imported from the original sample.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

saraphdnz · Posted 07-01-2020 05:29 PM

Thanks @Kurt_Bremser for your help. I am now able to run the program.

proc transpose
  data=mydata.return
  out=mydata.long (
  drop=_name_
  rename=(col1=y)
   where=(y ne .)
  )
 ;
by id;
var _:;
run;

Regards, Sara

SAS Innovate 2025: Register Now