Calculations based on a string variable

[AlG]

Hi friends,

My panel dataset includes data about the tweets of several firms over many days. I have a variable X1 whose values are yes or no. I want to create two variables: one to capture the total number of a firm's tweets in each month for which X1 was yes, and another variable to capture for what portion of a firm's tweets in each month X1 was "yes"   (number of tweets in a month in which X1 took on "yes" divided by the total number of tweets in the month). I used the code below, however I am getting an error which states "The AVG summary function requires a numeric argument". Is there any way to accomplish this without having to convert the yes/no values to zero and one? The problem is I want to calculate the sum and average for a lot of other variables too, and don't want to go through converting all of them to binary values.

The second error that I am getting says the order by "Statement is not valid or it is used out of proper order."

proc sql;
    create table Twitter6 as
    select Year, Week, author, Date, X1, sum(X1= 'yes') AS Sum_X1_Y, avg(X1= 'yes') 
    AS avg_X1_Y
    from Twitter5
    GROUP BY Year, Week, author;
    order by author, date;
 quit;

[SASKiwi]

Using a CASE expression should do it:

sum(case when X1= 'yes' then 1 else 0 end)

[AlG]

Thanks so much @SASKiwi 

Can I use the same code for calculating the average (i.e. by replacing sum with avg)?

[Kurt_Bremser]

Maxim 4. Try It.

---

@AlG wrote:

Thanks so much @SASKiwi 

Can I use the same code for calculating the average (i.e. by replacing sum with avg)?

---

[SASKiwi]

@AlG - I don't know what average you are trying to calculate. Please define it. An average only makes sense if you are analysing a continuous variable like age, not yeses or noes in this case.

[AlG]

@SASKiwi 

Sorry what you say makes sense (average is not appropriate is this is not a continuous variable). I basically want to calculate the number of tweets (in a month) for which X1 is yes divided by the total number of tweets in that month.

[SASKiwi]

@AlG - In that case, @Kurt_Bremser 's method will give you what you want.

[Kurt_Bremser]

The average of a binary (0/1) variable gives a percentage.

[Kurt_Bremser · Accepted solution]

See here an example for the average of a binary:

data have;
input author $ month :yymmdd10. x1 $;
format month yymmd7.;
datalines;
A 2020-03-01 Yes
A 2020-03-01 No
A 2020-03-01 Yes
;

proc sql;
create table want as
select
  author,
  month,
  count(*) as total,
  sum (case when x1 = "Yes" then 1 else 0 end) as sum_y,
  avg (case when x1 = "Yes" then 1 else 0 end) format=percent7.2 as pct_x1_y
from have
group by author, month;
quit;

Result:

author	month	total	sum_y	pct_x1_y
A	2020-03	3	2	66.7%

[AlG]

Thanks so much @Kurt_Bremser 

I used the code you suggested. It is not giving any error for the sum function, but does give an error whenever I have used the avg one. Below are the last few lines of the log.

1905      BiLSTM_boastful, sum (case when BiLSTM_boastful = "yes" then 1 else 0 end) AS
1905! Sum_BiLSTM_boastful, avg (case when BiLSTM_boastful = "yes" then 1 else 0 end)
1905! format=percent7.2 as pct_BiLSTM_boastful
1906      from Twitter6
1907      GROUP BY Year, GovWeek, MergeName;
ERROR: The AVG summary function requires a numeric argument.
1908
1909  quit;
NOTE: The SAS System stopped processing this step because of errors.

[Kurt_Bremser]

Please post the complete log of the step. My code as posted was tested and WORKS.

[AlG]

@Kurt_Bremser Below is the full log. It is long. I hope posting such a long log is appropriate.

1826  proc sql;
1827      create table Twitter7 as
1828      select v1, author, name, MergeName, industry, country, state, city, url, following,
1828! contents, posttitle, cleantext, noun, datecst, Date, Year, Month, Day, GovMonth, GovWeek,
1828! emotion, sentiment_cat, count(*) as total, pronoun_i, sum(pronoun_i) AS Sum_pronoun_i,
1828! avg(pronoun_i) AS avg_pronoun_i, pronoun_we, sum(pronoun_we) AS Sum_pronoun_we,
1828! avg(pronoun_we) AS avg_pronoun_we, valence, avg(valence) AS avg_valence, P_competence,
1828! sum(P_competence) AS Sum_P_competence, avg(P_competence) AS avg_P_competence,
1829      P_excitement, sum(P_excitement) AS Sum_P_excitement, avg(P_excitement) AS
1829! avg_P_excitement,
1830      P_ruggedness, sum(P_ruggedness) AS Sum_P_ruggedness, avg(P_ruggedness) AS
1830! avg_P_ruggedness,
1831      P_sincerity, sum(P_sincerity) AS Sum_P_sincerity, avg(P_sincerity) AS avg_P_sincerity,
1832      P_sophistication, sum(P_sophistication) AS Sum_P_sophistication, avg(P_sophistication)
1832! AS avg_P_sophistication,
1833      concreteness, averageconcreteness, avg(averageconcreteness) AS avg_averageconcreteness,
1834      svm_price, sum (case when svm_price= "yes" then 1 else 0 end) AS Sum_svm_price, avg
1834! (case when svm_price= "yes" then 1 else 0 end) format=percent7.2 as pct_svm_price,
1835      svm_non_price, sum (case when svm_non_price= "yes" then 1 else 0 end) AS
1835! Sum_svm_non_price, avg (case when svm_non_price = "yes" then 1 else 0 end) format=percent7.2
1835!  as pct_svm_non_price,
1836      svm_brand, sum (case when svm_brand= "yes" then 1 else 0 end) AS Sum_svm_brand, avg
1836! (case when svm_brand= "yes" then 1 else 0 end) format=percent7.2 as pct_svm_brand,
1837      svm_sentiment, sum (case when svm_sentiment = "yes" then 1 else 0 end) AS
1837! Sum_svm_Positive, avg (case when svm_sentiment = "yes" then 1 else 0 end) format=percent7.2
1837! as pct_svm_Positive,
1838      sum (case when svm_sentiment = "neutral" then 1 else 0 end) AS Sum_svm_neutral, avg
1838! (case when svm_sentiment = "neutral" then 1 else 0 end) format=percent7.2 as
1838! pct_svm_neutral,
1839      svm_humorous, sum (case when svm_humorous = "yes" then 1 else 0 end) AS
1839! Sum_svm_humorous, avg (case when svm_humorous = "yes" then 1 else 0 end) format=percent7.2
1839! as pct_svm_humorous,
1840      svm_advert, sum (case when svm_advert = "yes" then 1 else 0 end) AS Sum_svm_advert, avg
1840! (case when svm_advert = "yes" then 1 else 0 end) format=percent7.2 as pct_svm_advert,
1841      sum (case when svm_advert = "no" then 1 else 0 end) AS Sum_svm_Nonadvert, avg (case when
1841!  svm_advert = "no" then 1 else 0 end) format=percent7.2 as pct_svm_Nonadvert,
1842      svm_calltoaction, sum (case when svm_calltoaction = "yes" then 1 else 0 end) AS
1842! Sum_svm_calltoaction, avg (case when svm_calltoaction = "yes" then 1 else 0 end)
1842! format=percent7.2 as pct_svm_calltoaction,
1843      svm_firm_focused, sum (case when svm_firm_focused = "yes" then 1 else 0 end) AS
1843! Sum_svm_firm_focused, avg (case when svm_firm_focused = "yes" then 1 else 0 end)
1843! format=percent7.2 as pct_svm_firm_focused,
1844      sum (case when svm_firm_focused = "no" then 1 else 0 end) AS Sum_svm_Nonfirm_focused,
1844! avg (case when svm_firm_focused = "no" then 1 else 0 end) format=percent7.2 as
1844! pct_svm_Nonfirm_focused,
1845      svm_boastful, sum (case when svm_boastful = "yes" then 1 else 0 end) AS
1845! Sum_svm_boastful, avg (case when svm_boastful = "yes" then 1 else 0 end) format=percent7.2
1845! as pct_svm_boastful,
1846      Naive_price, sum (case when Naive_price = "yes" then 1 else 0 end) AS Sum_Naive_price,
1846! avg (case when Naive_price = "yes" then 1 else 0 end) format=percent7.2 as pct_Naive_price,
1847      Naive_non_price, sum (case when Naive_non_price = "yes" then 1 else 0 end) AS
1847! Sum_Naive_non_price, avg (case when Naive_non_price = "yes" then 1 else 0 end)
1847! format=percent7.2 as pct_Naive_non_price,
1848      Naive_brand, sum (case when Naive_brand = "yes" then 1 else 0 end) AS Sum_Naive_brand,
1848! avg (case when Naive_brand = "yes" then 1 else 0 end) format=percent7.2 as pct_Naive_brand,
1849      Naive_sentiment, sum (case when Naive_sentiment = "yes" then 1 else 0 end) AS
1849! Sum_Naive_Positive, avg (case when Naive_sentiment = "yes" then 1 else 0 end)
1849! format=percent7.2 as pct_Naive_Positive,
1850      sum (case when Naive_sentiment = "neutral" then 1 else 0 end) AS Sum_Naive_neutral, avg
1850! (case when Naive_sentiment = "neutral" then 1 else 0 end) format=percent7.2 as
1850! pct_Naive_neutral,
1851      Naive_humorous, sum (case when Naive_humorous = "yes" then 1 else 0 end) AS
1851! Sum_Naive_humorous, avg (case when Naive_humorous = "yes" then 1 else 0 end)
1851! format=percent7.2 as pct_Naive_humorous,
1852      Naive_advert, sum (case when Naive_advert = "yes" then 1 else 0 end) AS
1852! Sum_Naive_advert, avg (case when Naive_advert = "yes" then 1 else 0 end) format=percent7.2
1852! as pct_Naive_advert,
1853      sum (case when Naive_advert = "no" then 1 else 0 end) AS Sum_Naive_Nonadvert, avg (case
1853! when Naive_advert = "no" then 1 else 0 end) format=percent7.2 as pct_Naive_Nonadvert,
1854      naivebayes_calltoaction, sum (case when naivebayes_calltoaction = "yes" then 1 else 0
1854! end) AS Sum_naive_calltoaction, avg (case when naivebayes_calltoaction = "yes" then 1 else 0
1854!  end) format=percent7.2 as pct_naive_calltoaction,
1855      Naive_firm_focused, sum (case when Naive_firm_focused = "yes" then 1 else 0 end) AS
1855! Sum_Naive_firm_focused, avg (case when Naive_firm_focused = "yes" then 1 else 0 end)
1855! format=percent7.2 as pct_Naive_firm_focused,
1856      sum (case when Naive_firm_focused = "no" then 1 else 0 end) AS
1856! Sum_Naive_Nonfirm_focused, avg (case when Naive_firm_focused = "no" then 1 else 0 end)
1856! format=percent7.2 as pct_Naive_Nonfirm_focused,
1857      Naive_boastful, sum (case when Naive_boastful = "yes" then 1 else 0 end) AS
1857! Sum_Naive_boastful, avg (case when Naive_boastful = "yes" then 1 else 0 end)
1857! format=percent7.2 as pct_Naive_boastful,
1858      Knn_price, sum (case when Knn_price = "yes" then 1 else 0 end) AS Sum_Knn_price, avg
1858! (case when Knn_price = "yes" then 1 else 0 end) format=percent7.2 as pct_Knn_price,
1859      Knn_non_price, sum (case when Knn_non_price = "yes" then 1 else 0 end) AS
1859! Sum_Knn_non_price, avg (case when Knn_non_price = "yes" then 1 else 0 end) format=percent7.2
1859!  as pct_Knn_non_price,
1860      Knn_brand, sum (case when Knn_brand = "yes" then 1 else 0 end) AS Sum_Knn_brand, avg
1860! (case when Knn_brand = "yes" then 1 else 0 end) format=percent7.2 as pct_Knn_brand,
1861      Knn_sentiment, sum (case when Knn_sentiment = "yes" then 1 else 0 end) AS
1861! Sum_Knn_Positive, avg (case when Knn_sentiment = "yes" then 1 else 0 end) format=percent7.2
1861! as pct_Knn_Positive,
1862      sum (case when Knn_sentiment = "neutral" then 1 else 0 end) AS Sum_Knn_neutral, avg
1862! (case when Knn_sentiment = "neutral" then 1 else 0 end) format=percent7.2 as pct_Knn_neutral
1862!  ,
1863      Knn_humorous, sum (case when Knn_humorous = "yes" then 1 else 0 end) AS
1863! Sum_Knn_humorous, avg (case when Knn_humorous = "yes" then 1 else 0 end) format=percent7.2
1863! as pct_Knn_humorous,
1864      Knn_advert, sum (case when Knn_advert = "yes" then 1 else 0 end) AS Sum_Knn_advert, avg
1864! (case when Knn_advert = "yes" then 1 else 0 end) format=percent7.2 as pct_Knn_advert,
1865      sum (case when Knn_advert = "no" then 1 else 0 end) AS Sum_Knn_Nonadvert, avg (case when
1865!  Knn_advert = "no" then 1 else 0 end) format=percent7.2 as pct_Knn_Nonadvert,
1866      knn_calltoaction, sum (case when knn_calltoaction = "yes" then 1 else 0 end) AS
1866! Sum_knn_calltoaction, avg (case when knn_calltoaction = "yes" then 1 else 0 end)
1866! format=percent7.2 as pct_knn_calltoaction,
1867      Knn_firm_focused, sum (case when Knn_firm_focused = "yes" then 1 else 0 end) AS
1867! Sum_Knn_firm_focused, avg (case when Knn_firm_focused = "yes" then 1 else 0 end)
1867! format=percent7.2 as pct_Knn_firm_focused,
1868      sum (case when Knn_firm_focused = "no" then 1 else 0 end) AS Sum_Knn_Nonfirm_focused,
1868! avg (case when Knn_firm_focused = "no" then 1 else 0 end) format=percent7.2 as
1868! pct_Knn_Nonfirm_focused,
1869      Knn_boastful, sum (case when Knn_boastful = "yes" then 1 else 0 end) AS
1869! Sum_Knn_boastful, avg (case when Knn_boastful = "yes" then 1 else 0 end) format=percent7.2
1869! as pct_Knn_boastful,
1870      Cnn_price, sum (case when Cnn_price = "yes" then 1 else 0 end) AS Sum_Cnn_price, avg
1870! (case when Cnn_price = "yes" then 1 else 0 end) format=percent7.2 as pct_Cnn_price,
1871      Cnn_non_price, sum (case when Cnn_non_price = "yes" then 1 else 0 end) AS
1871! Sum_Cnn_non_price, avg (case when Cnn_non_price = "yes" then 1 else 0 end) format=percent7.2
1871!  as pct_Cnn_non_price,
1872      Cnn_brand, sum (case when Cnn_brand = "yes" then 1 else 0 end) AS Sum_Cnn_brand, avg
1872! (case when Cnn_brand = "yes" then 1 else 0 end) format=percent7.2 as pct_Cnn_brand,
1873      Cnn_sentiment, sum (case when Cnn_sentiment = "yes" then 1 else 0 end) AS
1873! Sum_Cnn_Positive, avg (case when Cnn_sentiment = "yes" then 1 else 0 end) format=percent7.2
1873! as pct_Cnn_Positive,
1874      sum (case when Cnn_sentiment = "neutral" then 1 else 0 end) AS Sum_Cnn_neutral, avg
1874! (case when Cnn_sentiment = "neutral" then 1 else 0 end) format=percent7.2 as
1874! pct_Cnn_neutral,
1875      Cnn_humorous, sum (case when Cnn_humorous = "yes" then 1 else 0 end) AS
1875! Sum_Cnn_humorous, avg (case when Cnn_humorous = "yes" then 1 else 0 end) format=percent7.2
1875! as pct_Cnn_humorous,
1876      Cnn_advert, sum (case when Cnn_advert = "yes" then 1 else 0 end) AS Sum_Cnn_advert, avg
1876! (case when Cnn_advert = "yes" then 1 else 0 end) format=percent7.2 as pct_Cnn_advert,
1877      sum (case when Cnn_advert = "no" then 1 else 0 end) AS Sum_Cnn_Nonadvert, avg (case when
1877!  Cnn_advert = "no" then 1 else 0 end) format=percent7.2 as pct_Cnn_Nonadvert,
1878      cnn_calltoaction, sum (case when cnn_calltoaction = "yes" then 1 else 0 end) AS
1878! Sum_cnn_calltoaction, avg (case when cnn_calltoaction = "yes" then 1 else 0 end)
1878! format=percent7.2 as pct_cnn_calltoaction,
1879      Cnn_firm_focused, sum (case when Cnn_firm_focused = "yes" then 1 else 0 end) AS
1879! Sum_Cnn_firm_focused, avg (case when Cnn_firm_focused = "yes" then 1 else 0 end)
1879! format=percent7.2 as pct_Cnn_firm_focused,
1880      sum (case when Cnn_firm_focused = "no" then 1 else 0 end) AS Sum_Cnn_Nonfirm_focused,
1880! avg (case when Cnn_firm_focused = "no" then 1 else 0 end) format=percent7.2 as
1880! pct_Cnn_Nonfirm_focused,
1881      Cnn_boastful, sum (case when Cnn_boastful = "yes" then 1 else 0 end) AS
1881! Sum_Cnn_boastful, avg (case when Cnn_boastful = "yes" then 1 else 0 end) format=percent7.2
1881! as pct_Cnn_boastful,
1882      LSTM_price, sum (case when LSTM_price = "yes" then 1 else 0 end) AS Sum_LSTM_price, avg
1882! (case when LSTM_price = "yes" then 1 else 0 end) format=percent7.2 as pct_LSTM_price,
1883      LSTM_non_price, sum (case when LSTM_non_price = "yes" then 1 else 0 end) AS
1883! Sum_LSTM_non_price, avg (case when LSTM_non_price = "yes" then 1 else 0 end)
1883! format=percent7.2 as pct_LSTM_non_price,
1884      LSTM_brand, sum (case when LSTM_brand = "yes" then 1 else 0 end) AS Sum_LSTM_brand, avg
1884! (case when LSTM_brand = "yes" then 1 else 0 end) format=percent7.2 as pct_LSTM_brand,
1885      LSTM_sentiment, sum (case when LSTM_sentiment = "yes" then 1 else 0 end) AS
1885! Sum_LSTM_Positive, avg (case when LSTM_sentiment = "yes" then 1 else 0 end)
1885! format=percent7.2 as pct_LSTM_Positive,
1886      sum (case when LSTM_sentiment = "neutral" then 1 else 0 end) AS Sum_LSTM_neutral, avg
1886! (case when LSTM_sentiment = "neutral" then 1 else 0 end) format=percent7.2 as
1886! pct_LSTM_neutral,
1887      LSTM_humorous, sum (case when LSTM_humorous = "yes" then 1 else 0 end) AS
1887! Sum_LSTM_humorous, avg (case when LSTM_humorous = "yes" then 1 else 0 end) format=percent7.2
1887!  as pct_LSTM_humorous,
1888      LSTM_advert, sum (case when LSTM_advert = "yes" then 1 else 0 end) AS Sum_LSTM_advert,
1888! avg (case when LSTM_advert = "yes" then 1 else 0 end) format=percent7.2 as pct_LSTM_advert,
1889      sum (case when LSTM_advert = "no" then 1 else 0 end) AS Sum_LSTM_Nonadvert, avg (case
1889! when LSTM_advert = "no" then 1 else 0 end) format=percent7.2 as pct_LSTM_Nonadvert,
1890      lstm_calltoaction, sum (case when lstm_calltoaction = "yes" then 1 else 0 end) AS
1890! Sum_lstm_calltoaction, avg (case when lstm_calltoaction = "yes" then 1 else 0 end)
1890! format=percent7.2 as pct_lstm_calltoaction,
1891      LSTM_firm_focused, sum (case when LSTM_firm_focused = "yes" then 1 else 0 end) AS
1891! Sum_LSTM_firm_focused, avg (case when LSTM_firm_focused = "yes" then 1 else 0 end)
1891! format=percent7.2 as pct_LSTM_firm_focused,
1892      sum (case when LSTM_firm_focused = "no" then 1 else 0 end) AS Sum_LSTM_Nonfirm_focused,
1892! avg (case when LSTM_firm_focused = "no" then 1 else 0 end) format=percent7.2 as
1892! pct_LSTM_Nonfirm_focused,
1893      LSTM_boastful, sum (case when LSTM_boastful = "yes" then 1 else 0 end) AS
1893! Sum_LSTM_boastful, avg (case when LSTM_boastful = "yes" then 1 else 0 end) format=percent7.2
1893!  as pct_LSTM_boastful,
1894      BiLSTM_price, sum (case when BiLSTM_price = "yes" then 1 else 0 end) AS
1894! Sum_BiLSTM_price, avg (case when BiLSTM_price = "yes" then 1 else 0 end) format=percent7.2
1894! as pct_BiLSTM_price,
1895      BiLSTM_non_price, sum (case when BiLSTM_non_price = "yes" then 1 else 0 end) AS
1895! Sum_BiLSTM_non_price, avg (case when BiLSTM_non_price = "yes" then 1 else 0 end)
1895! format=percent7.2 as pct_BiLSTM_non_price,
1896      BiLSTM_brand, sum (case when BiLSTM_brand = "yes" then 1 else 0 end) AS
1896! Sum_BiLSTM_brand, avg (case when BiLSTM_brand = "yes" then 1 else 0 end) format=percent7.2
1896! as pct_BiLSTM_brand,
1897      BiLSTM_sentiment, sum (case when BiLSTM_sentiment = "yes" then 1 else 0 end) AS
1897! Sum_BiLSTM_Positive, avg (case when BiLSTM_sentiment = "yes" then 1 else 0 end)
1897! format=percent7.2 as pct_BiLSTM_Positive,
1898      sum (case when BiLSTM_sentiment = "neutral" then 1 else 0 end) AS Sum_BiLSTM_neutral ,
1898! avg (case when BiLSTM_sentiment = "neutral" then 1 else 0 end) format=percent7.2 as
1898! pct_BiLSTM_neutral,
1899      BiLSTM_humorous, sum (case when BiLSTM_humorous = "yes" then 1 else 0 end) AS
1899! Sum_BiLSTM_humorous, avg (case when BiLSTM_humorous= "yes" then 1 else 0 end)
1899! format=percent7.2 as pct_BiLSTM_humorous,
1900      BiLSTM_advert, sum (case when BiLSTM_advert = "yes" then 1 else 0 end) AS
1900! Sum_BiLSTM_advert, avg (case when BiLSTM_advert = "yes" then 1 else 0 end) format=percent7.2
1900!  as pct_BiLSTM_advert,
1901      sum (case when BiLSTM_advert = "no" then 1 else 0 end) AS Sum_BiLSTM_Nonadvert, avg
1901! (case when BiLSTM_advert = "no" then 1 else 0 end) format=percent7.2 as
1901! pct_BiLSTM_Nonadvert,
1902      bilstm_calltoaction, sum (case when bilstm_calltoaction = "yes" then 1 else 0 end) AS
1902! Sum_bilstm_calltoaction, avg (case when bilstm_calltoaction = "yes" then 1 else 0 end)
1902! format=percent7.2 as pct_bilstm_calltoaction,
1903      BiLSTM_firm_focused, sum (case when BiLSTM_firm_focused = "yes" then 1 else 0 end) AS
1903! Sum_BiLSTM_firm_focused, avg (case when BiLSTM_firm_focused = "yes" then 1 else 0 end)
1903! format=percent7.2 as pct_BiLSTM_firm_focused,
1904      sum (case when BiLSTM_firm_focused = "no" then 1 else 0 end) AS
1904! Sum_BiLSTM_Nonfirm_focused, avg (case when BiLSTM_firm_focused = "no" then 1 else 0 end)
1904! format=percent7.2 as pct_BiLSTM_Nonfirm_focused,
1905      BiLSTM_boastful, sum (case when BiLSTM_boastful = "yes" then 1 else 0 end) AS
1905! Sum_BiLSTM_boastful, avg (case when BiLSTM_boastful = "yes" then 1 else 0 end)
1905! format=percent7.2 as pct_BiLSTM_boastful
1906      from Twitter6
1907      GROUP BY Year, GovWeek, MergeName;
ERROR: The AVG summary function requires a numeric argument.
1908
1909  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.08 seconds
      cpu time            0.07 seconds

[Kurt_Bremser]

Maxim 3: Know Your Data.

One of the variables you use in the other avg() calls is of type character, and needs to somehow be converted to numeric first.

[AlG]

@Kurt_Bremser Thank so much. The code worked!

One last question: Why am I getting the following error (these are the last few lines of the log; the preceding parts are the same as the log I posted earlier)?

493      from Twitter6
494      GROUP BY Year, GovWeek, MergeName;
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table WORK.TWITTER7 created, with 132946 rows and 246 columns.

495      order by author, date;
         -----
         180
ERROR 180-322: Statement is not valid or it is used out of proper order.

496  quit;
NOTE: The SAS System stopped processing this step because of errors.

[Tom]

There is no ORDER BY statement in SQL.  You have to include the ORDER BY clause as part of the SELECT clause.

That is why I always place semi-colon that ends a multiple line statement on a separate line.  So it is not missed by something scanning the code.  Just like placing an END statement or a RUN statement on its own line.

create table xxx as
  select 
   ....
   ....
  order by ...
;