I tested the sample code with the way that novinnosrin suggested in Linux server. I got the single quoted string. However, I didn't get the single quoted string when I change '|' to | in my real code. This code is existed in a macro program within a %DO loop. It's exactly written as %LET var_b1 = %scan(&var_b, &i.,|), and &var_b has a value exactly like the way as 'Apple, Orange and Peach (AOP) Garden Fruits'|'Apple Pine and Banana'. The reason I need a single quoted value for this &var_b is because I can't use '&var_b' to get the value of this macro variable, and I can't use "&var_b" because this part is inside an existing " " sentence. I couldn't understand why it doesn't work. There is no error or warning message in the log except at the execution step that uses the value of the macro variable which has no quotations. In terms of presenting the real code and log, I need to copy all the contents of log that are related to these several lines of code from different macro programs. I can't do this because of the sensitivities of data controlled by rules in our company. ... View more

Thanks, PaigeMiller ! My goal is to have a single quoted value for a macro variable, i.e., I need the value exactly to be as this; 'Apple, Orange and Peach (AOP) Garden Fruits' ... View more

I tested this simple solution in Base SAS. It works. That's great! However, when I run this method with my real program in linux server, it still has no single quotations. ... View more

Okay. I would bypass this approach if there was no solution for it. Thank you so much, KurtBremser ! ... View more

"%let deployMode = "dev";" exists in a configuration program which defines the whole system environment for multitasks, and my code is just for one of the tasks. I need to use unquoted "dev" for pooled data file names, and their paths, so I use &env to get the unquoted value from &deployMode. Of cause, I can assign &env directly using "%let env = dev;" and no issue occurred, but this is not my intention here as I wish &env could catch the value automatically from the settings existed in the environment. ... View more

'%let deployMode = "dev";' is actually not in my code. It's a global macro variable that has many usages in our system. Let's not focus on modifying this macro variable to find a solution for the error. ... View more

I can't change &deployMode like this: %let deployMode = dev; because this macro variable already exists in our system, and is used for the entire infrastructure. I did not complicate my life unnecessarily. On the contrary, this change will make the life of our group much complicated. ... View more

Thanks for the advice. I re-run the code with the first line "options mprint;". Here is the new code: options mprint; %macro CsvExl_Import(location=, filename=, DBMS=, outfile=); proc import datafile="&location./&filename..&DBMS." DBMS= &DBMS. REPLACE out= work.&outfile.; getnames=yes; guessingrows=200000; run; %mend CsvExl_Import; %let deployMode = "dev"; /*##################################*/ /* create data sets */ data one; input IDnumber $ Week1 Week16; WeightLoss2=Week1-Week16; datalines; 2477 195 163 2431 220 198 2456 173 155 2412 135 116 ; data two; input IDnumber $ Week1 Week16; WeightLoss2=Week1-Week16; datalines; 4321 321 768 3245 342 754 1324 123 546 6237 543 121 ; /* Write to csv files */ proc export data=work.one outfile='C:/cctem/2020q3/dev/dev_sample_2019.csv' dbms=csv replace; run; proc export data=work.two outfile='C:/cctem/2020q3/dev/dev_sample_2020.csv' dbms=csv replace; run; /*##################################*/ %let env = %qsysfunc(compress(&deployMode.,%str(%"))); %let years = 19 20; %let impl = 2020q3; %put &env; %macro build_table(); %local i; %do i = 1 %to %sysfunc(countw(&years)); /* Define values for macro variables of Year(s) and File name(s)*/ %let years&i = %scan(&years, &i); %let File_&i = &env._sample_20&&years&i; data three; set sashelp.class; run; /* Read data of each year */ %CsvExl_Import(location= C:/cctem/&impl./&env. ,filename= &&File_&i. ,DBMS= csv ,outfile= &&File_&i.); %end; %mend; /* Run macro program */ %build_table(); Here is the log: 509 options mprint; 510 511 %macro CsvExl_Import(location=, filename=, DBMS=, outfile=); 512 proc import datafile="&location./&filename..&DBMS." 513 DBMS= &DBMS. 514 REPLACE 515 out= work.&outfile.; 516 getnames=yes; 517 guessingrows=200000; 518 run; 519 %mend CsvExl_Import; 520 521 %let deployMode = "dev"; 522 523 /*##################################*/ 524 /* create data sets */ 525 data one; 526 input IDnumber $ Week1 Week16; 527 WeightLoss2=Week1-Week16; 528 datalines; NOTE: The data set WORK.ONE has 4 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.03 seconds cpu time 0.03 seconds 533 ; 534 535 data two; 536 input IDnumber $ Week1 Week16; 537 WeightLoss2=Week1-Week16; 538 datalines; NOTE: The data set WORK.TWO has 4 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.01 seconds 543 ; 544 /* Write to csv files */ 545 546 proc export data=work.one 547 outfile='C:/cctem/2020q3/dev/dev_sample_2019.csv' 548 dbms=csv 549 replace; 550 run; 551 /********************************************************************** 552 * PRODUCT: SAS 553 * VERSION: 9.4 554 * CREATOR: External File Interface 555 * DATE: 17SEP20 556 * DESC: Generated SAS Datastep Code 557 * TEMPLATE SOURCE: (None Specified.) 558 ***********************************************************************/ 559 data _null_; 560 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */ 561 %let _EFIREC_ = 0; /* clear export record count macro variable */ 562 file 'C:/cctem/2020q3/dev/dev_sample_2019.csv' delimiter=',' DSD DROPOVER lrecl=32767; 563 if _n_ = 1 then /* write column names or labels */ 564 do; 565 put 566 "IDnumber" 567 ',' 568 "Week1" 569 ',' 570 "Week16" 571 ',' 572 "WeightLoss2" 573 ; 574 end; 575 set WORK.ONE end=EFIEOD; 576 format IDnumber $8. ; 577 format Week1 best12. ; 578 format Week16 best12. ; 579 format WeightLoss2 best12. ; 580 do; 581 EFIOUT + 1; 582 put IDnumber $ @; 583 put Week1 @; 584 put Week16 @; 585 put WeightLoss2 ; 586 ; 587 end; 588 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */ 589 if EFIEOD then call symputx('_EFIREC_',EFIOUT); 590 run; NOTE: The file 'C:/cctem/2020q3/dev/dev_sample_2019.csv' is: Filename=C:\cctem\2020q3\dev\dev_sample_2019.csv, RECFM=V,LRECL=32767,File Size (bytes)=0, Last Modified=17Sep2020:11:51:40, Create Time=17Sep2020:10:50:30 NOTE: 5 records were written to the file 'C:/cctem/2020q3/dev/dev_sample_2019.csv'. The minimum record length was 15. The maximum record length was 33. NOTE: There were 4 observations read from the data set WORK.ONE. NOTE: DATA statement used (Total process time): real time 0.04 seconds cpu time 0.04 seconds 4 records created in C:/cctem/2020q3/dev/dev_sample_2019.csv from WORK.ONE. NOTE: "C:/cctem/2020q3/dev/dev_sample_2019.csv" file was successfully created. NOTE: PROCEDURE EXPORT used (Total process time): real time 0.23 seconds cpu time 0.12 seconds 591 592 proc export data=work.two 593 outfile='C:/cctem/2020q3/dev/dev_sample_2020.csv' 594 dbms=csv 595 replace; 596 run; 597 /********************************************************************** 598 * PRODUCT: SAS 599 * VERSION: 9.4 600 * CREATOR: External File Interface 601 * DATE: 17SEP20 602 * DESC: Generated SAS Datastep Code 603 * TEMPLATE SOURCE: (None Specified.) 604 ***********************************************************************/ 605 data _null_; 606 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */ 607 %let _EFIREC_ = 0; /* clear export record count macro variable */ 608 file 'C:/cctem/2020q3/dev/dev_sample_2020.csv' delimiter=',' DSD DROPOVER lrecl=32767; 609 if _n_ = 1 then /* write column names or labels */ 610 do; 611 put 612 "IDnumber" 613 ',' 614 "Week1" 615 ',' 616 "Week16" 617 ',' 618 "WeightLoss2" 619 ; 620 end; 621 set WORK.TWO end=EFIEOD; 622 format IDnumber $8. ; 623 format Week1 best12. ; 624 format Week16 best12. ; 625 format WeightLoss2 best12. ; 626 do; 627 EFIOUT + 1; 628 put IDnumber $ @; 629 put Week1 @; 630 put Week16 @; 631 put WeightLoss2 ; 632 ; 633 end; 634 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */ 635 if EFIEOD then call symputx('_EFIREC_',EFIOUT); 636 run; NOTE: The file 'C:/cctem/2020q3/dev/dev_sample_2020.csv' is: Filename=C:\cctem\2020q3\dev\dev_sample_2020.csv, RECFM=V,LRECL=32767,File Size (bytes)=0, Last Modified=17Sep2020:11:51:41, Create Time=17Sep2020:10:50:30 NOTE: 5 records were written to the file 'C:/cctem/2020q3/dev/dev_sample_2020.csv'. The minimum record length was 16. The maximum record length was 33. NOTE: There were 4 observations read from the data set WORK.TWO. NOTE: DATA statement used (Total process time): real time 0.04 seconds cpu time 0.04 seconds 4 records created in C:/cctem/2020q3/dev/dev_sample_2020.csv from WORK.TWO. NOTE: "C:/cctem/2020q3/dev/dev_sample_2020.csv" file was successfully created. NOTE: PROCEDURE EXPORT used (Total process time): real time 0.21 seconds cpu time 0.11 seconds 637 638 /*##################################*/ 639 640 %let env = %qsysfunc(compress(&deployMode.,%str(%"))); 641 %let years = 19 20; 642 %let impl = 2020q3; 643 %put &env; dev 644 645 646 647 %macro build_table(); 648 %local i; 649 650 %do i = 1 %to %sysfunc(countw(&years)); 651 /* Define values for macro variables of Year(s) and File name(s)*/ 652 %let years&i = %scan(&years, &i); 653 %let File_&i = &env._sample_20&&years&i; 654 655 data three; 656 set sashelp.class; 657 run; 658 659 /* Read data of each year */ 660 %CsvExl_Import(location= C:/cctem/&impl./&env. 661 ,filename= &&File_&i. 662 ,DBMS= csv 663 ,outfile= &&File_&i.); 664 665 %end; 666 667 %mend; 668 669 /* Run macro program */ 670 %build_table(); MPRINT(BUILD_TABLE): data three; MPRINT(BUILD_TABLE): set sashelp.class; MPRINT(BUILD_TABLE): run; NOTE: There were 19 observations read from the data set SASHELP.CLASS. NOTE: The data set WORK.THREE has 19 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.01 seconds ERROR: "WORK." is not a valid name. NOTE 138-205: Line generated by the macro variable "OUTFILE". 1 work.dev_sample_2019 ------------ 22 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, FILE, OUT, REPLACE, TABLE, _DEBUG_. NOTE: Line generated by the macro variable "OUTFILE". 1 work.dev_sample_2019 ------------ 202 ERROR 202-322: The option or parameter is not recognized and will be ignored. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.01 seconds cpu time 0.01 seconds NOTE: The SAS System stopped processing this step because of errors. MPRINT(CSVEXL_IMPORT): proc import datafile="C:/cctem/2020q3/dev/dev_sample_2019.csv" DBMS= csv REPLACE out= work.dev_sample_2019 getnames=yes; MPRINT(CSVEXL_IMPORT): guessingrows=200000; MPRINT(CSVEXL_IMPORT): run; MPRINT(BUILD_TABLE): ; MPRINT(BUILD_TABLE): data three; MPRINT(BUILD_TABLE): set sashelp.class; MPRINT(BUILD_TABLE): run; NOTE: There were 19 observations read from the data set SASHELP.CLASS. NOTE: The data set WORK.THREE has 19 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.01 seconds ERROR: "WORK." is not a valid name. NOTE 138-205: Line generated by the macro variable "OUTFILE". 1 work.dev_sample_2020 ------------ 22 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, FILE, OUT, REPLACE, TABLE, _DEBUG_. NOTE: Line generated by the macro variable "OUTFILE". 1 work.dev_sample_2020 ------------ 202 ERROR 202-322: The option or parameter is not recognized and will be ignored. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.01 seconds cpu time 0.01 seconds NOTE: The SAS System stopped processing this step because of errors. MPRINT(CSVEXL_IMPORT): proc import datafile="C:/cctem/2020q3/dev/dev_sample_2020.csv" DBMS= csv REPLACE out= work.dev_sample_2020 getnames=yes; MPRINT(CSVEXL_IMPORT): guessingrows=200000; MPRINT(CSVEXL_IMPORT): run; MPRINT(BUILD_TABLE): ; ... View more

It can be accomplished now with my task of importing .TXT files with head lines and tail lines.   Thanks for everyone's help! ... View more

Thank you, ChrisNZ ! If I had a extra line below the last line as a tail and I wanted to exclude it (different .txt files have different amount of the rows, so that we can't identify and use the row number to locate the tail for all .txt files), what will the code look like? ... View more

The .txt file is my source data and actually contains large amount of columns and rows. It has headers (title, descriptions, and even tail at the bottom of the data body). There are many .txt files to be imported. To manually input the names of column is not practical. The example I presented here is just a simplified one that represent the structure of .txt data file. If a program can import this example .txt file and output the SAS data that I want, my mission is accomplished. For now, I have to manually read these .txt files using Excel, delete the unwanted parts, and save it into a csv file,  then these .csv files are subjected for SAS to import.    Thanks for all the suggestions and helps! ... View more

It can be replicated by the following steps: 1) Use Notepad to save the following text as a .txt file in your folder: Title desc1 date name type bday alan card 11/15/1950 bob e-card 08/23/1951   2) Assign macro &path. with your path. Use the following code to import the attached .txt file: %let path = "yourpath"; proc import datafile = "&path.\text.txt" out = data1 dbms = dlm replace; delimiter = ' '; getnames= yes; run; SAS read the text successfully. 5 rows and 3 columns created in work.data1 from the text.txt file. But the value in work.data1 is: column name         Title   VAR2   VAR3 row1                      desc1 row2                      date row3                      name   type     bday row4                      alan     card     11/15/1950 row5                      bob     e-card   08/23/1951   I want a data like this, only have three columns and three rows including the column names: column name         name   type     bday row2                      alan     card     11/15/1950 row3                      bob     e-card   08/23/1951   If use the following code: proc import datafile = "&path.\text.txt" out = data1 dbms = dlm replace; delimiter = ' '; getnames= yes; namerow= 4; datarow= 5; run; then failed with the error message: 110 proc import datafile = "&path.\text.txt" 111 out = data1 112 dbms = dlm replace; NOTE: The previous statement has been deleted. NOTE: The previous statement has been deleted. 113 delimiter = ' '; 114 getnames= yes; 115 namerow= 4; ------- 180 ERROR 180-322: Statement is not valid or it is used out of proper order. 116 datarow= 5; 117 run; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.03 seconds cpu time 0.03 seconds     ... View more

Thanks. I don't need the top three lines. And I actually have a data set with hundreds of columns, so I want to avoid using inputting column names manually. The best way is just to read the data existed in the .txt file. ... View more